Create a Linked Picture and Toggle it on and off
In this short article I’m going to show you how to create an Excel linked picture and how to toggle it on and off with a small piece of VBA code.
This process is especially useful if you want to show the user some summarized data on another sheet without actually taking them to that sheet.
The Excel linked picture becomes an object on our destination sheet that we make visible and invisible with some VBA code.
I have used this very successfully in many files for years now to improve the user experience and provide needed information when decisions are required based on data in another sheet.
- Go to the source sheet for your picture. In this demonstration we are using the staff leave planner and our source sheet is named “Planner”
- Select the range that you would like the picture to be taken from
- In the Ribbon in the Clipboard section click Copy
- Navigate to the destination sheet and click any cell
- In the Ribbon in the Clipboard section click the Paste drop-down arrow and select Linked Picture
Follow the two illustrations below through steps 1 to 4.
Record a macro to find its name
We now need to find the name of this shape. The easiest way to do this is to record a macro that selects the shape and moves it.
- From the Developer tab choose Record Macro
- Leave all of these settings as default
- Select the shape and move it slightly
- Stop Recording
Adjusting the code
Here is the recorded code. We do not need it all, but what it does tell us is the name of this picture. This is how VBA will recognise our linked picture.
We can modify the code very simply. Notice that we have used the Not keyword in order to reverse the first statement. Simple but effective.
Note: Make sure you have your picture number in this code and that there is a space between the word Picture and the number.
ActiveSheet.Shapes.Range("Picture 7").Visible = Not ActiveSheet.Shapes.Range("Picture 7").Visible
- Add a button (shape) to your destination sheet where the shape resides. Select Insert on the ribbon and choose Shape.
- Right click the button and assign the macro “Toggle”
- Add appropriate text to the shape (button)