[Excel VBA] Auto-Insert Images into Shapes by Number & Delete Shapes While Keeping Buttons

This article introduces two useful Excel VBA macros for managing images and shapes.

1. Auto-Insert Macro: Automatically inserts images from a folder into shapes that have matching numbers (e.g., puts “photo1.jpg” into the shape named “Image1”).
2. Smart Delete Macro: Deletes all shapes on a sheet except for specific buttons.

By combining these codes, you can easily automate image layout and reset your sheet for the next task.

1. Macro to Auto-Insert Images into Matching Shapes

This macro allows you to select a folder containing numbered image files. It then reads files like “1.jpg” or “image1.jpg” and sets them as the background for shapes named “Image1”, “Image2”, etc.

VBA Code

Sub InsertImagesIntoShapes_MatchingNumber()
    Dim ws As Worksheet
    Dim imgFolder As String
    Dim imgPath As String
    Dim imgFile As String
    Dim shapeName As String
    Dim folderDialog As FileDialog
    Dim imgNumber As Integer

    ' Select a folder using a dialog box
    Set folderDialog = Application.FileDialog(msoFileDialogFolderPicker)
    folderDialog.Title = "Please select the image folder"
    If folderDialog.Show <> -1 Then Exit Sub ' Exit if cancelled
    imgFolder = folderDialog.SelectedItems(1) & "\" ' Get folder path

    ' Specify the sheet to paste images into
    Set ws = ThisWorkbook.Sheets("Sheet2")

    ' Read files from the folder
    imgFile = Dir(imgFolder & "*.jpg")  ' Target only .jpg files

    ' Loop through files and insert into shapes
    Do While imgFile <> ""
        imgPath = imgFolder & imgFile

        ' Extract the number from the filename
        imgNumber = ExtractNumber(imgFile)
        
        If imgNumber > 0 Then
            ' Define the target shape name (e.g., "Image1")
            ' Note: Ensure your shapes are named "Image1", "Image2", etc.
            shapeName = "Image" & imgNumber
            
            ' Insert image if the corresponding shape exists
            On Error Resume Next
            With ws.Shapes(shapeName)
                .Fill.UserPicture imgPath ' Set image as background
                .TextFrame2.TextRange.Text = "" ' Clear any text
            End With
            On Error GoTo 0
        End If

        ' Get the next file
        imgFile = Dir
    Loop

    MsgBox "Image insertion complete.", vbInformation
End Sub

' Function to extract numbers from a filename
Function ExtractNumber(ByVal fileName As String) As Integer
    Dim i As Integer, numStr As String
    numStr = ""
    For i = 1 To Len(fileName)
        ' Check if the character is a number
        If IsNumeric(Mid(fileName, i, 1)) Then
            numStr = numStr & Mid(fileName, i, 1)
        End If
    Next i
    
    If numStr <> "" Then
        ExtractNumber = CInt(numStr)
    Else
        ExtractNumber = 0
    End If
End Function

Explanation of the Code

• Matching Numbers: The ExtractNumber function pulls the number from the filename (e.g., extracts 1 from photo1.jpg). The macro then looks for a shape named "Image" & 1 (i.e., “Image1”) on the sheet.
• Inserting the Image: It uses .Fill.UserPicture to set the image as the shape’s background. It also clears any text inside the shape so it doesn’t overlap the image.

2. Macro to Delete Shapes Except Buttons

This macro deletes all shapes on the sheet but preserves specific buttons (e.g., your “Run” or “Reset” buttons). This is perfect for clearing a form or dashboard without deleting your control buttons.

VBA Code

Sub ResetShapesExceptButtons()
    Dim ws As Worksheet
    Dim shp As Shape

    ' Specify the sheet to clean up
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' Define button names to keep (Modify these to match your actual button names)
    Const button1 As String = "Button_Reset"
    Const button2 As String = "Button_Insert"

    ' Loop through all shapes and delete non-buttons
    For Each shp In ws.Shapes
        ' If the shape name is NOT button1 AND NOT button2, delete it
        If shp.Name <> button1 And shp.Name <> button2 Then
            shp.Delete
        End If
    Next shp

    MsgBox "All shapes except the buttons have been deleted.", vbInformation
End Sub

Explanation of the Code

• Specifying Buttons: We define the names of the buttons we want to protect as constants (button1, button2). You should change these strings to match the actual names of the buttons on your Excel sheet.
• Deletion Logic: The If statement checks the name of every shape. If the name does not match your button names, the shape is deleted.

Summary

These two macros streamline the process of managing visual elements in Excel.

1. Use InsertImagesIntoShapes_MatchingNumber to automatically populate your report or dashboard with images.
2. Use ResetShapesExceptButtons to instantly clear the sheet for a fresh start, without losing your control buttons.

Try adding these to your workflow to save time on manual formatting!