Hiding Picture & Sheet from checkbox

[NiallCollins]

Good Morning All,

I need to hide a sheet & an image when a checkbox is unchecked. Its so when the checkbox is unchecked, The image, which is a shortcut to the page when clicked, is no longer visible. I can get either or working but need to know how to do both together.

Thanks in advance
Niall

 

[Yongle]

By "page" do you mean "sheet" ?

Is the checkbox active-x ?

What is the shortcut ?
- is it a hyperlink ?
- is it an assigned macro ? (please post the code )
- or something else (please explain)

How many similar checkboxes are there ?

 

[NiallCollins]

I've been using form control Checkboxes and the image is a macro shortcut to the correct sheet. Self teaching myself this but tried both active x and form control, i can get the image to hide on form control and the sheet to hide on Active X but struggling with getting the other action to work together with it. There will be 16 Checkboxes and associated sheets together.

Happy to switch around to active x or form control, whichever works best.

Here is an example of the shortcut image macro, (ADP is my image file name)

Sub ADP_Click()
    Sheets("Automatic Doors").Select
End Sub

 

[Yongle]

I will post code later today for you
Are you happy to rename your picture the same as the sheet name and also make checkbox caption the same as the sheet name?
Can then call the same macro for EVERY checkbox rather than specifying each shape and sheet separately

thanks

 

[NiallCollins]

That's absolutely wonderful thank you.

 

[Yongle]

Here you go
Put code in a module like Module 1
Test it to see if it does what you want and then I'll explain the code in detail for you

Private Sub HideSheetAndImage(aCheckBox As Shape)
    Dim BoxChecked As Boolean, BoxCaption As String, BoxImage As Shape, BoxSheet As Worksheet
    With aCheckBox.OLEFormat.Object
        BoxChecked = (.Value = 1)
        BoxCaption = .Caption
    End With
    Set BoxImage = ActiveSheet.Shapes(BoxCaption)
    Set BoxSheet = Sheets(BoxCaption)
   
    BoxImage.Visible = Not BoxChecked
    BoxSheet.Visible = Not BoxChecked
End Sub

And you also need ONE of these for EVERY checkbox in the same module

Sub CheckBox1_Click()
    Call HideSheetAndImage(ActiveSheet.Shapes(Application.Caller))
End Sub

So using your earlier example
1 the sheet is named Automatic Doors
2 the image is also named Automatic Doors
3 the caption for its check box is Automatic Doors

 

[Yongle]

I should have mentioned in post#6 that the code is written for Form Control Chechboxes

 

[NiallCollins]

Hero. Giving it a whirl now!

 

[NiallCollins]

Thank you so much. It works a treat but only one thing, Its the wrong way round. The image and sheet are showing when the checkbox is unchecked

 

[Yongle]

In that case you want this,
Have also reduced the number of lines

Private Sub HideSheetAndImage(aCheckBox As Shape)
    Dim BoxChecked As Boolean, BoxCaption As String
    With aCheckBox.OLEFormat.Object
        BoxChecked = (.Value = 1)
        BoxCaption = .Caption
    End With
    ActiveSheet.Shapes(BoxCaption).Visible = BoxChecked
    Sheets(BoxCaption).Visible = BoxChecked
End Sub