Author: Oscar Cronquist Article last updated on June 09, 2020

Show hide a picture

This article explains how to hide a specific image in Excel using a shape as a button. If the user clicks the shape the image disappears, click again and it is visible.

The following animated image shows when you click the shape, the picture is hidden. Click 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 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 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. Click "Shapes" button located on the ribbon. A pop-up menu appears, see image above.
  3. Click on a shape to select it. I chose a rounded rectangle.
  4. Click 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 click with left mouse button on a shape and the sizing handles appear.

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

Click 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. Click 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. Click "Insert" on the menu, see image above.
  3. Click "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. Right-click on the shape. A pop-up menu appears.
  2. Click "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. Click 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

Download Excel file


Show-hide-picture.xlsm

Back to top

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 […]

How to change a picture in a worksheet dynamically [VBA]

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 […]

Locate a shape in a workbook

Add pictures to a chart axis

This picture below shows you a column chart with pictures (flags) below each column. Watch this video to learn how […]

Add pictures to a chart axis

Recommended links

Back to top