Use VBA code to insert and remove pictures in an Excel worksheet

This is another brief foray into VBA programming. When you click the Insert Image button, the following VBA code executes.
' Insert a picture at the selection.The code makes a string containing filters for the types of files it wants to select. It calls Application.GetOpenFilename to let the user pick a file. It then calls InsertPicture to insert the image. InsertPicture calls the active worksheet's Picture collection's Insert method. It then sets the picture's Top and Left properties to the Top and Left of the location passed into the routine. This positions the picture in the desired cell. When you click the Remove Image button, the following code executes.
Sub Button1_Click()
Dim filters As String
Dim filename As Variant
' Define the file selection filters.
filters = "Image Files,*.bmp;*.tif;*.jpg;*.png,PNG (*.png),*.png,TIFF (*.tif),*.tif,JPG (*.jpg),*.jpg,All Files (*.*),*.*"
' Get the file name.
filename = Application.GetOpenFilename( _
filters, 0, "Select Image", "Take It", False)
If filename = False Then Exit Sub
' Insert the picture.
InsertPicture CStr(filename), Application.Selection
End Sub
' Insert a picture into a cell.
Sub InsertPicture(filename As String, location As Range)
Dim pic As Picture
Set pic = ActiveSheet.Pictures.Insert(filename)
pic.Top = location.Top
pic.Left = location.Left
End Sub
' Remove pictures from the selected cell.The button's event handler simply calls RemovePictures. That method loops through all of the pictures on the active worksheet. If it finds a picture with Top and Left in the active cell, the code removes it.
Sub Button2_Click()
RemovePictures Application.Selection
End Sub
' Remove pictures from a cell.
Sub RemovePictures(location As Range)
Dim i As Integer
Dim pic As Picture
For i = 1 To ActiveSheet.Pictures.Count
Set pic = ActiveSheet.Pictures(i)
If pic.Top = location.Top And _
pic.Left = location.Left _
Then
pic.Delete
End If
Next i
End Sub


Comments