How to refer on a picture on a different worksheet

[Sari]

Hi all,

I would like to setup some toggle buttons. In my document I have one main-sheet (dashboard) and 29 sub-sheets. On all 29 worksheets I need one toggle button which changes the embedded picture if the value is true or false.
To make sure I don't create an Excel file which has an enourmous size, I would like to insert the 2 pictures on the dashboard and refer from each worksheet toggle button on it.
Unfortunately it only works if I insert the pictures on the same sheet where the toggle button is and this would mean that I need to add 58 pictures to the Excel file.
My file is now almost 6MB, so I need to take care that I won't get too big.
This is the code which works if I add it to every worksheet:


If ToggleButton1.Value = True Then
ToggleButton1.Picture = Image6.Picture
Worksheets("Dashboard").Shapes("Rounded Rectangle 7").TextFrame.Characters.Font.ColorIndex = 1
Worksheets("Dashboard").Shapes("Rounded Rectangle 7").Fill.Transparency = False
ActiveSheet.Range("A1").Select
Else
ToggleButton1.Picture = Image5.Picture
Worksheets("Dashboard").Shapes("Rounded Rectangle 7").Fill.Transparency = 0.5
ActiveSheet.Range("A1").Select

But how do I need to change the code to refer on the pictures on the worksheet?

I tried this:
ToggleButton1.Picture = Worksheets("Dashboard").Shapes.Image3.Picture
but this doesn't work.

Thanks in advance for your help!!

Greets,
Sari

[mdmackillop]

Can you post a sample workbook showing your desired setup.

[Sari]

Can you post a sample workbook showing your desired setup.


Hi mdmackillop,

unfortunately the file is too large for uploading (it's already 6.2MB)...
Actually following should happen:
If the toggle button is pressed (value true) than a specific shape on the dashboard needs to change its color and the toggle button should change the picture regarding the value of the toggle button.
Colorchange for the shape is no issue, this works perfectly, but it seems that the toggle button doesn't understand where the picture is I refer to in the code.
So I think that the "address" of the picture is wrong:

Worksheets("Dashboard").Shapes.Image3.Picture

There is no error message, but the button doesn't change, the picture is the same if the value is true or false.

Please let me know if you need more information.

Greets,
Sari

[mdmackillop]

Hi
I'm not suggesting the whole workbook. A sheet to hold your images and a couple of typical worksheets only showing toggle buttons etc. No confidential info.

[Paul_Hossler]

I put a place holder shape/picture on each of the data sheets and then copied the appropriate Dashboard sheet shape/picture on top

Not exactly the question but might be an OK workaround technique





Option Explicit
Private Sub ToggleButton1_Click()
Dim r As Range

With ActiveSheet
On Error Resume Next
.Shapes("Dilbert").Delete
.Shapes("Alice").Delete
On Error GoTo 0

Set r = .Shapes("PlaceHolder").TopLeftCell

If .ToggleButton1 Then
Worksheets("Dashboard").Shapes("Dilbert").Copy
Else
Worksheets("Dashboard").Shapes("Alice").Copy
End If

r.Select
.Paste
r.Select
End With


End Sub

[mdmackillop]

Give this a try. I've used Paul's layout as a starting point. Images are removed from each sheet when deactivated.

[Sari]

This helped me a lot! Thank you all very much!!