Unhide Another Sheet if any of 10 checkboxes is checked

[Newbie_Reena]

Hi There!

I have an excel file with Control Checkboxes in cells A45, C45, E45, G45, etc.

I'd like to have a macro where if ANY of the checkboxes listed above is selected, then Sheet 10 labelled "Advanced" will unhide. Otherwise if all checkboxes are unchecked then the sheet remains hidden or goes back to hidden.

Any help is GREATLY appreciated!

 

[oriwitt]

For this you could use ActiveX check boxes. You can find these in the controls under the developer tab under Controls -> Insert -> ActiveX Controls -> Check Box (ActiveX Control).

For my example I drew 3 of these ActiveX checkboxes, with 3 sheets in a default workbook, and successfully hid sheet 2 when all of the check boxes were unchecked, and showed it when any or all of them were checked.

Option Explicit


Public Sub UpdateCheckboxes()


'Evaluate which checkboxes are checked.


'Declare an Object type variable to identify the kind of ActiveX control (checkboxes in this example) that are selected.
Dim xObj As OLEObject


'Turn off screen updating
Application.ScreenUpdating = False


'Set desired worksheet to hidden by default on interaction with ANY ActiveX CheckBox
Worksheets(2).Visible = False


For Each xObj In ActiveSheet.OLEObjects
    If TypeName(xObj.Object) = "CheckBox" Then
        If xObj.Object.Value = True Then
            Worksheets(2).Visible = True
        End If
    End If
Next xObj

'Turn on screen updating
Application.ScreenUpdating = True


End Sub


Sub CheckBox1_Click()


Call UpdateCheckboxes


End Sub


Sub CheckBox2_Click()


Call UpdateCheckboxes


End Sub


Sub CheckBox3_Click()


Call UpdateCheckboxes


End Sub

You can extend this for as many Checkbox#_Click() events as you would like. However this won't work unless the only ActiveX check boxes in the Activesheet are those you are considering, in which case you might have to make a collection of just the ones you need to work with.

 

[oriwitt]

Alternatively, this would work, same worksheet, with Form Control check boxes instead of ActiveX check boxes.

Option Explicit


Sub UpdateCheckboxes()


Dim cb As Shape


'Turn off screen updating
Application.ScreenUpdating = False


'Set desired worksheet to hidden by default on interaction with ANY ActiveX CheckBox
Worksheets(2).Visible = False


'Loop through Checkboxes
  For Each cb In ActiveSheet.Shapes
    If cb.Type = msoFormControl Then
      If cb.FormControlType = xlCheckBox Then
        If cb.ControlFormat.Value = 1 Then Worksheets(2).Visible = True
      End If
    End If
  Next cb




'Turn on screen updating
Application.ScreenUpdating = True


End Sub


Sub CheckBox4_Click()


Call UpdateCheckboxes


End Sub


Sub CheckBox5_Click()


Call UpdateCheckboxes


End Sub


Sub CheckBox6_Click()


Call UpdateCheckboxes


End Sub

Again, this would be limited by if you had other Form Control check boxes in the ActiveSheet. As before, the best course then would probably be to make a collection of only those check box objects you wanted to consider and iterate through that collection instead of all in the ActiveSheet.Shapes collection.

 

[Newbie_Reena]

Hi there! I have very specific checkboxes (about 10) out of 50 or so on the page. I actually need to replicate the code for all 50-60 to different sheets. How would the code change if you don't mind?

 

[oriwitt]

With Form Control or ActiveX Control checkboxes?

 

[Newbie_Reena]

All are Form Control. I can change it to ActiveX if it really makes a difference.

 

[oriwitt]

The only solution I am capable of thinking of is to have a separate UpdateCheckboxesSheet#() for each worksheet you wanted to hide/unhide, and have the relevant CheckBox#_Click() events call UpdateCheckboxesSheet#() for it's respective sheet. It isn't elegant at all but it's all that would be available without a UserForm and event driven handlers.

Note the Range(Array()) sets which checkboxes you are considering, which is filled in order of when the checkboxes were added. If you do not know which one is which because you aren't sure what order they were added, because you've renamed them in the Name Box, or just generally can't figure it out, in the VBE you can type ? ActiveSheet.Shapes(#).Name to find which indexed Shape refers to which Check Box name. You can find the check box name by right clicking on the relevant check box and looking at the Name Box next to the Formula Bar.

Option Explicit


Sub UpdateCheckboxesSheet2()


Dim cb2 As Shape


'Turn off screen updating
Application.ScreenUpdating = False


'Set desired worksheet to hidden by default on interaction with ANY ActiveX CheckBox
Worksheets(2).Visible = False


'Loop through Checkboxes
For Each cb2 In ActiveSheet.Shapes.Range(Array(1, 2, 3))
    If cb2.Type = msoFormControl Then
        If cb2.FormControlType = xlCheckBox Then
            If cb2.ControlFormat.Value = 1 Then Worksheets(2).Visible = True
        End If
    End If
Next cb2


'Turn on screen updating
Application.ScreenUpdating = True


End Sub


Sub UpdateCheckboxesSheet3()


Dim cb3 As Shape


'Turn off screen updating
Application.ScreenUpdating = False


'Set desired worksheet to hidden by default on interaction with ANY ActiveX CheckBox
Worksheets(3).Visible = False


'Loop through Checkboxes
For Each cb3 In ActiveSheet.Shapes.Range(Array(4, 5, 6))
    If cb3.Type = msoFormControl Then
        If cb3.FormControlType = xlCheckBox Then
            If cb3.ControlFormat.Value = 1 Then Worksheets(3).Visible = True
        End If
    End If
Next cb3


'Turn on screen updating
Application.ScreenUpdating = True


End Sub


Sub CheckBox1_Click()


Call UpdateCheckboxesSheet2


End Sub


Sub CheckBox2_Click()


Call UpdateCheckboxesSheet2


End Sub


Sub CheckBox3_Click()


Call UpdateCheckboxesSheet2


End Sub


Sub CheckBox4_Click()


Call UpdateCheckboxesSheet3


End Sub


Sub CheckBox5_Click()


Call UpdateCheckboxesSheet3


End Sub


Sub CheckBox6_Click()


Call UpdateCheckboxesSheet3


End Sub