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
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
- 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.
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.
How to assign a macro to shape
- 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
Recommended articles
Recommended articles
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
Recommended articles
This article demonstrates how to locate a shape in Excel programmatically based on the value stored in the shape. The […]
Recommended articles
This article demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country […]
Recommended links
- VBA Code to insert, move, delete and control pictures
- Show Picture on Mouse Hover with VBA
- Showing a picture from a list of pictures
If then else statement category
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to […]
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Excel categories
15 Responses to “Show / hide a picture using a button”
Leave a Reply
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.
Wonderful work
Is it possible to make the code to run on Office 2003
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.
i think you can name the picture so that the name stays the same (formulas tab -> define name.
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.
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
[…] https://www.get-digital-help.com/2013/10/25/show-and-hide-a-picture-vba/ […]
how to make it at bottom and center?
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 Withhow 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?
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 SubSub 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.
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")
Thank you for this code it works perfect. I was wondering if there is a way to apply the macro to multiple buttons to show different pictures. For example, I have a large list of items with images for each one and want to create a button for each image. Is it possible to do this without having to create a macro for every single button/image? Thank you.
Yes, this small video demonstrates a macro that adds a button to the upper right corner of all images in the active worksheet.
Link to animation
Sub InsertRoundedRectangle() Dim ws As Worksheet Dim shp As Shape Set ws = ActiveSheet For Each shp In ws.Shapes If shp.Type = msoPicture Then Dim leftPos As Double Dim topPos As Double leftPos = shp.Left + shp.Width topPos = shp.Top Set shp = ws.Shapes.AddShape(msoShapeRoundedRectangle, leftPos, topPos - 25, 75, 25) With shp .TextFrame.Characters.Text = "Show" .TextFrame.Characters.Font.Size = 14 .TextFrame.Characters.Font.Color = RGB(0, 0, 0) .TextFrame.Characters.Font.Bold = True .TextFrame.Characters.Font.Name = "Arial" .TextFrame.VerticalAlignment = xlCenter .TextFrame.HorizontalAlignment = xlCenter .Fill.ForeColor.RGB = RGB(255, 255, 255) .Line.ForeColor.RGB = RGB(0, 0, 0) .Line.Weight = 2 .Adjustments.Item(1) = 0.5 End With End If Next shp End Sub