Add 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.
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

The code makes a string containing filters for the types of files it will let you select. It calls Application.GetOpenFilename to let you pick a file and, if you pick a file, it calls InsertPicture to insert the image.

The InsertPicture method 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.

' Remove pictures from the selected cell.
Sub Button2_Click()
    If (TypeOf Application.Selection Is Range) Then
        RemovePictures Application.Selection
    ElseIf (TypeOf Application.Selection Is Picture) Then
        MsgBox "I don't know how to remove pictures form a " & _
    End If
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 _
        End If
    Next i
End Sub

The button’s event handler determines whether the current selection is a range, picture, or something else. If the selection is a range, the code calls the RemovePictures method. 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.

If the selection is a picture, the Button2_Click event handler deletes it.

If the selections is something else, the code displays an error message.

Download Example   Follow me on Twitter   RSS feed   Donate

This entry was posted in Excel, Office, VBA and tagged , , , , , , , , , . Bookmark the permalink.