Author: Oscar Cronquist Article last updated on February 14, 2023

Show hide a picture

This article explains how to hide a specific image in Excel using a shape as a button. If the user press with left mouse button ons the shape the image disappears, press with left mouse button on again and it is visible.

The following animated image shows when you press with left mouse button on the shape, the picture is hidden. Press with left mouse button on it again and the picture shows up. The macro changes the shape text based on the state of the image.

Show hide picture vba1

Move and press with left mouse button on 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 how to accomplish that.

Instructions

First, let's create a button (shape). This time I will not use a regular button (Form Controls), I will use a shape. I chose a shape with rounded corners.

Back to top

Insert a shape

Show hide a picture insert shape 1

  1. Go to tab "Insert" on the ribbon.
  2. Press with left mouse button on "Shapes" button located on the ribbon. A pop-up menu appears, see image above.
  3. Press with mouse on a shape to select it. I chose a rounded rectangle.
  4. Press and hold with left mouse button on the worksheet. Drag with mouse to place and create the shape.

Show hide a picture select shape

The image above shows a new shape. The round white circles around the shape are sizing handles.

The sizing handles indicate that the shape is selected. To select a shape simply press with left mouse button on with left mouse button on a shape and the sizing handles appear.

Press with left mouse button on anywhere outside the shape to deselect it. To move a shape press and hold with left mouse button on it and then drag with mouse to the new location.

Press and hold with left mouse button on a sizing handle, then drag with mouse to resize the shape.

Press and hold Alt key to align shape with cell grid while moving or resizing the shape.

Back to top

Insert a picture

  1. Copy a picture.
  2. Paste a picture to a worksheet.
  3. Select the picture to see the name. The name appears in the name bar, see image below.
    select picture to see the name

Note, you change the name of the shape using the name bar. Press with left mouse button on the text in the name bar, edit the text then press Enter.

Back to top

VBA Code

'Name macro
Sub Macro1()

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With ActiveSheet.Shapes("Rounded Rectangle 4").TextFrame2.TextRange.Characters

   'Check if shape text is equal to "Hide"
   If .Text = "Hide" Then

      'Change shape text to "Show"
      .Text = "Show"

      'Hide shape
      ActiveSheet.Shapes("Picture 1").Visible = False

   'Continue here if shape is not equal to "Hide"
   Else

      'Change text to "Hide"
      .Text = "Hide"

      'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
      With ActiveSheet.Shapes("Rounded Rectangle 4")

         'Move image named "Picture1" based to lower right corner of shape
         ActiveSheet.Shapes("Picture 1").Left = .Left + .Width
         ActiveSheet.Shapes("Picture 1").Top = .Top + .Height

         'Show image
         ActiveSheet.Shapes("Picture 1").Visible = True
      End With
    End If
End With
End Sub

Back to top

Where to put the code?

vb editor - insert a module

  1. Press Alt + F11 to start the Visual Basic Editor (VBE).
  2. Press with left mouse button on "Insert" on the menu, see image above.
  3. Press with left mouse button on "Module".
  4. Paste VBA code to window.
  5. Exit VB Editor and return to Excel.
Note, save the workbook with file extension *.xlsm (macro-enabled workbook) to keep the code attached to the workbook.

Back to top

How to assign a macro to shape

Show hide a picture shape assign macro

  1. Press with right mouse button on on the shape. A pop-up menu appears.
  2. Press with left mouse button on "Assign Macro...", see image above. A dialog box shows up.
  3. Select Macro1 in the list.
    Show hide a picture shape assign macro dialog box
  4. Press with left mouse button on OK button to assign Macro1 to the shape.

Back to top

Positioning the picture

The following examples demonstrate how to place a picture in relation to a shape using VBA.

Place image next to the upper right corner of a shape

upper right corner

These VBA lines change the position of the image to the upper right corner of the shape. The picture name is "Picture 1" and the last line below makes it visible.

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With ActiveSheet.Shapes("Rounded Rectangle 4")

'Place image next to the upper right corner
ActiveSheet.Shapes("Picture 1").Left = .Left + .Width
ActiveSheet.Shapes("Picture 1").Top = .Top - ActiveSheet.Shapes("Picture 1").Height

'Show image
ActiveSheet.Shapes("Picture 1").Visible = True
End With

Back to top

Place image next to the upper left corner of a shape

upper left corner

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With ActiveSheet.Shapes("Rounded Rectangle 4")

'Place image next to the upper left corner
ActiveSheet.Shapes("Picture 1").Left = .Left - ActiveSheet.Shapes("Picture 1").Width
ActiveSheet.Shapes("Picture 1").Top = .Top - ActiveSheet.Shapes("Picture 1").Height

'Show image
ActiveSheet.Shapes("Picture 1").Visible = True
End With

Back to top

Place image next to the lower left corner of a shape

lower left corner

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With ActiveSheet.Shapes("Rounded Rectangle 4")

'Place image next to the lower left corner
ActiveSheet.Shapes("Picture 1").Left = .Left - ActiveSheet.Shapes("Picture 1").Width
ActiveSheet.Shapes("Picture 1").Top = .Top + .Height

'Show image
ActiveSheet.Shapes("Picture 1").Visible = True
End With

Back to top

Get the Excel file


Show-hide-picture.xlsm

Back to top

Recommended articles

Recommended articles

How to change a picture in a worksheet dynamically [VBA]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]

Recommended articles

Locate a shape in a workbook
This article demonstrates how to locate a shape in Excel programmatically based on the value stored in the shape. The […]

Recommended articles

Add pictures to a chart axis
This article demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country […]

Recommended links

Back to top