Have you ever seen dashboards where you can click a shape and a picture shows up. If you click it again the picture disappears. This post explains how to do that. The following picture shows when you click the shape, the picture is hidden. Click it again and the picture shows up.

Show hide picture vba1

Move and click the shape and the picture is automatically repositioned to the lower right shape corner. You can also position the picture wherever you like, I'll show later in this post.

Instructions

First, let´s create a button (shape).

Insert a shape

  1. Go to tab "Insert" on the ribbon
  2. Click Shapes button
    insert tab on the ribbon
  3. Select a shape
  4. Drag on sheet to create the shape

Insert a picture

  1. Paste a picture to sheet
  2. Select the picture to see the name
    select picture to see the name

VBA Code

Sub Macro1()
    With ActiveSheet.Shapes("Rounded Rectangle 4").TextFrame2.TextRange.Characters
        If .Text = "Hide" Then
            .Text = "Show"
            ActiveSheet.Shapes("Picture 1").Visible = False
        Else
            .Text = "Hide"
            With ActiveSheet.Shapes("Rounded Rectangle 4")
                ActiveSheet.Shapes("Picture 1").Left = .Left + .Width
                ActiveSheet.Shapes("Picture 1").Top = .Top + .Height
                ActiveSheet.Shapes("Picture 1").Visible = True
            End With
        End If
    End With
End Sub
  1. Go to VB Editor (Alt + F11)
  2. Click "Insert" on the menu
  3. Click "Module"
  4. Paste vba code
    vb editor - insert a module
  5. Exit VB Editor and return to excel
  6. Right click on the shape
  7. Click "Assign Macro"
  8. Select Macro1
  9. Click OK

Explaining the vba code

Step 1 - The With statement execute a series of statements that repeatedly refers to a single object or structure

With ActiveSheet.Shapes("Rounded Rectangle 4").TextFrame2.TextRange.Characters

Step 2 - Check if text in shape "Rounded Rectangle 4" is "Hide"

If .Text = "Hide" Then

Step 3 - If so, change text to "Show"

.Text = "Show"

Step 4 - and hide Picture 1

ActiveSheet.Shapes("Picture 1").Visible = False

 Step 5 - If text in shape is not "Hide", change text to "Hide"

Else
.Text = "Hide"

Step 6 - Also, move shape to the lower right shape corner and make it visible

With ActiveSheet.Shapes("Rounded Rectangle 4")
ActiveSheet.Shapes("Picture 1").Left = .Left + .Width
ActiveSheet.Shapes("Picture 1").Top = .Top + .Height
ActiveSheet.Shapes("Picture 1").Visible = True
End With

Step 7 - End If-then-Else statement

End If

Step 8 - End With statement

End With

Positioning the picture

Upper right corner

With ActiveSheet.Shapes("Rounded Rectangle 4")
ActiveSheet.Shapes("Picture 1").Left = .Left + .Width
ActiveSheet.Shapes("Picture 1").Top = .Top - ActiveSheet.Shapes("Picture 1").Height
ActiveSheet.Shapes("Picture 1").Visible = True
End With

upper right corner

Upper left corner

With ActiveSheet.Shapes("Rounded Rectangle 4")
ActiveSheet.Shapes("Picture 1").Left = .Left - ActiveSheet.Shapes("Picture 1").Width
ActiveSheet.Shapes("Picture 1").Top = .Top - ActiveSheet.Shapes("Picture 1").Height
ActiveSheet.Shapes("Picture 1").Visible = True
End With

upper left corner

Lower left corner

With ActiveSheet.Shapes("Rounded Rectangle 4")
ActiveSheet.Shapes("Picture 1").Left = .Left - ActiveSheet.Shapes("Picture 1").Width
ActiveSheet.Shapes("Picture 1").Top = .Top + .Height
ActiveSheet.Shapes("Picture 1").Visible = True
End With

lower left corner

Download excel *.xlsm file

Show - hide picture.xlsm