Show / hide a picture using a button

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.

What's on this page

Insert a shape
Insert an image
Show/Hide an image using a VBA macro
Where to put the code?
Link macro to shape
Place image next to the upper-right corner of shape
Place image next to the upper-left corner of shape
Place image next to the lower-left corner of shape
Get Excel *.xlsm file

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.

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.

Insert a shape

Show hide a picture insert shape 1

Go to tab "Insert" on the ribbon.
Press with left mouse button on "Shapes" button located on the ribbon. A pop-up menu appears, see image above.
Press with mouse on a shape to select it. I chose a rounded rectangle.
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.

Insert a picture

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

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.

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

Where to put the code?

Press Alt + F11 to start the Visual Basic Editor (VBE).
Press with left mouse button on "Insert" on the menu, see image above.
Press with left mouse button on "Module".
Paste VBA code to window.
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.

How to assign a macro to shape

Show hide a picture shape assign macro

Press with right mouse button on on the shape. A pop-up menu appears.
Press with left mouse button on "Assign Macro...", see image above. A dialog box shows up.
Select Macro1 in the list.
Press with left mouse button on OK button to assign Macro1 to the shape.

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

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

Place image next to the upper left corner of a shape

'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

Place image next to the lower left corner of a shape

'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

Get the Excel file

Show-hide-picture.xlsm

If then else statement category

Run a Macro from a Drop Down list [VBA]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]

Excel calendar [VBA]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]

Find the most/least consecutive repeated value [VBA]
This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to […]

How to create a list of comments from a worksheet programmatically
Did you know that you can select all cells containing comments in the current sheet? Press F5, press with left […]

How to use the IF THEN ELSE ELSEIF END IF statement [VBA]
This article demonstrates how to use the If ... Then statement in Visual Basic for Applications (VBA). You can combine […]

Macro category

Save invoice data [VBA]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]

Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]

Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]

Run a Macro from a Drop Down list [VBA]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]

Excel calendar [VBA]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]

Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]

Working with FILES
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]

Auto resize columns as you type
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]

How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]

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

Create a Print button [VBA]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]

Create a unique distinct list using Advanced Filter in a macro [VBA]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]

Create new worksheets programmatically based on values in a cell range [VBA]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]

Excel template: Getting Things Done [VBA]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]

How to highlight row of the selected cell programmatically
Today I would like to share with you these small event handler procedures that make it easier for you to […]

Find cells containing formulas with literal (hardcoded) values
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]

How to save specific multiple worksheets to a pdf file programmatically
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]

More than 1300 Excel formulas

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

13 Responses to “Show / hide a picture using a button”

mahmoud-lee says:

October 28, 2013 at 1:20 pm

Wonderful work

Reply
- mahmoud-lee says:
  
  October 28, 2013 at 1:36 pm
  
  Is it possible to make the code to run on Office 2003
JJ Barrera says:

July 22, 2014 at 7:09 pm

Hello, Thank you so much for this. It worked on my original worksheet, but once that sheet is copied, the macro no longer works because the new sheet has new shape names. Is there a way workaround for this? Like a way to make Excel not change the shape names when copied? I can't seem to find anything. Thanks again, this is the most useful tutorial so far that I've found on this. I'm a beginner, so I'm sure that many of the things I need to know are standard knowledge for most.

Reply
- s says:
  
  October 2, 2015 at 4:17 pm
  
  i think you can name the picture so that the name stays the same (formulas tab -> define name.
Rahul says:

October 14, 2015 at 11:59 am

hi,
i try this, it's working good. but i have two photos, when i press the button any one picture has to be shown and another one is to be hide is that possible.if it is possible pls tell that how to do.

regards
rahul.

Reply
Karteek says:

December 1, 2015 at 5:11 am

Hello,

I just wanted to know how can I retrieve an image from the desktop without selecting a range
for example:
I have created a macro and added a button in the ribbon, so what I wanted is that when i press with the left mouse button on that macro it should give a pop-up asking a name, after inserting the name it should pop-up image.
can anyone please help?

Thanks,
karteek

Reply
#Excel Super Links #19 – shared by David Hager | Excel For You says:

April 30, 2017 at 2:37 pm

[…] https://www.get-digital-help.com/2013/10/25/show-and-hide-a-picture-vba/ […]

Reply

azmi says:

June 14, 2017 at 6:31 pm

how to make it at bottom and center?

Oscar says:

June 15, 2017 at 11:59 am

azmi,

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

Micaela says:

August 16, 2018 at 4:21 am

how can i apply the vbacode to multiple buttons? like show/hide1 button show&hides only picture 1, then show/hide2 shows&hides only picture 2?

Oscar says:

August 16, 2018 at 8:29 am

Micaela

Assign Macro1 to the first button and Macro2 to the second button. Make sure the button and picture names are correct.

Sub Macro1()
    With ActiveSheet.Shapes("Rounded Rectangle 1").TextFrame2.TextRange.Characters
        If .Text = "Hide" Then
            .Text = "Show"
            ActiveSheet.Shapes("Picture 1").Visible = False
        Else
            .Text = "Hide"
            With ActiveSheet.Shapes("Rounded Rectangle 1")
                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

Sub Macro2()
    With ActiveSheet.Shapes("Rounded Rectangle 2").TextFrame2.TextRange.Characters
        If .Text = "Hide" Then
            .Text = "Show"
            ActiveSheet.Shapes("Picture 2").Visible = False
        Else
            .Text = "Hide"
            With ActiveSheet.Shapes("Rounded Rectangle 2")
                ActiveSheet.Shapes("Picture 2").Left = .Left + .Width
                ActiveSheet.Shapes("Picture 2").Top = .Top + .Height
                ActiveSheet.Shapes("Picture 2").Visible = True
            End With
        End If
    End With
End Sub

venkatesh says:

April 17, 2021 at 11:02 am

Sub Macro1()
With ActiveSheet.Shapes("Rounded Rectangle 1").TextFrame2.TextRange.Characters
If .Text = "Hide" Then
.Text = "Show"
ActiveSheet.Shapes("Picture 1").Visible = False
Else
.Text = "Hide"
With ActiveSheet.Shapes("Rounded Rectangle 1")
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

Sub Macro2()
With ActiveSheet.Shapes("Rounded Rectangle 2").TextFrame2.TextRange.Characters
If .Text = "Hide" Then
.Text = "Show"
ActiveSheet.Shapes("Picture 2").Visible = False
Else
.Text = "Hide"
With ActiveSheet.Shapes("Rounded Rectangle 2")
ActiveSheet.Shapes("Picture 2").Left = .Left + .Width
ActiveSheet.Shapes("Picture 2").Top = .Top + .Height
ActiveSheet.Shapes("Picture 2").Visible = True
End With
End If
End With
End Sub

Dear sir, this code perfectly working for me if i work on sheet1 in the workbook. if i move to second worksheet code is not running it is showing some error. Also it is not working in sheet1 also. after deleting the sheet2 and macro for the corresponding sheet then it starting resuming to work Perfectly again in sheet.

Reply
- Oscar says:
  
  April 19, 2021 at 9:16 am
  
  Replace all instances of ActiveSheet to Worksheets("Sheet1")
  
  Sheet1 is the name of your worksheet, they must correspond to the worksheet names in your workbook.
  
  For example:
  
  ActiveSheet.Shapes("Picture 2").Left = .Left + .Width
  
  becomes
  
  Worksheets("Sheet1").Shapes("Picture 2").Left = .Left + .WidthReplace all instances of ActiveSheet to Worksheets("Sheet1")

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form