How to Hide or Unhide Multiple Worksheets with a Checkbox in excel

Question

I am working in a workbook that has 10 worksheets in it. I have the main page Sheet10 where I want to put the check boxes to hide/unhide the other worksheets depending upon which checkbox is selected. I have all other worksheets set as "Very Hidden" This sheet should be the only one visible when I enter the workbook. Then once I check one of the Check boxes it unhides the associated worksheets. When it is unchecked it hides those sheets. What is happening everything is showing when I enter the workbook and it hides the worksheets that are not assigned to the check box I am selecting. When I unchecked the boxes everything shows.

I have added code to hide and show the worksheets but it isn't working as expected. It is only Hiding the worksheets it isn't unhiding the hidden sheets as I thought it would. I have all other worksheets listed as "Very Hidden". Also, if I click two buttons at the same time it goes funky and doesn't show the right worksheets

EDIT1 I added the code below to my macro module. I am getting errors on the If ctrl area with the multiple sheets to be shown. When I click on the optionbutton with the macro it gives me a compile error I must be missing something huge here.

optionbutton & macro

Sub Hide_Sheets()

    Select Case ctrl.Name
        Case "Option Button 1"
            If ctrl.ControlFormat.Value = 1 Then nohide = Array("Sheet2", "Sheet4", "Sheet5, "Sheet7", "Sheet9", "Sheet12")
        Case "Option Button 2"
            If ctrl.ControlFormat.Value = 1 Then nohide = Array("Sheet2", "Sheet3", "Sheet4", "Sheet5, "Sheet7", "Sheet9", "Sheet12", "Sheet14")
        Case "Option Button 3"
            If ctrl.ControlFormat.Value = 1 Then nohide = Array("Sheet13")
    End Select

Edit 2 I get the following error when I click on one of the option buttons. I found the issue I missed a ".

Macro Code Error

Edit 3 The below is the only code I have for this to try and show and hide the worksheets. It is in my macros module. With all the back and forth I am not sure if I have what I need now. I get a Debug error at (If ctrl.FormControlType =). So I have something drastically wrong. One added note. I cannot unselect any of the option buttons so they are not selected.

Sub Hide_Sheets()
    Dim ctrl As Shape, nohide As Variant
    For Each ctrl In Sheets(1).Shapes
        If ctrl.FormControlType = xlOptionButton Then
            Select Case ctrl.Name
                Case "Option Button 1"
                    If ctrl.ControlFormat.Value = 1 Then nohide = Array("Sheet2", "Sheet4", "Sheet5", "Sheet7", "Sheet9", "Sheet12")
                Case "Option Button 2"
                    If ctrl.ControlFormat.Value = 1 Then nohide = Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet7", "Sheet9", "Sheet12", "Sheet14")
                Case "Option Button 3"
                    If ctrl.ControlFormat.Value = 1 Then nohide = Array("Sheet13")
            End Select
        End If
    Next ctrl

    If Not IsEmpty(nohide) Then
        Dim ws As Worksheet
        For Each ws In Worksheets
             If IsError(Application.Match(ws.Name, nohide, 0)) Then
                 ws.Visible = False
            Else
                ws.Visible = True
            End If
        Next ws
        nohide = ""
End If

End Sub

What I want is a Workbook that has the main page shown when I enter and when I click one of the check boxes it shows or hides the related worksheets. I would like if you select more than one button to add those associated worksheets to visible. When all boxes are unchecked it "Very Hidden" them all.

Answer 1

I will recommend Option Buttons for this as it will be a bit easier, since those on a sheet are automatically linked and only one can be selected at a time (saves error handling on multiple clicked checkboxes)

All buttons are checked to the same macro, where you loop through the shapes on the sheet to determine what is selected to specify what sheets are to be visible (example code):

Dim ctrl As Shape, nohide As Variant
For Each ctrl In Sheets(1).Shapes
    If ctrl.FormControlType = xlOptionButton Then
        Select Case ctrl.Name
            Case "Option Button 1"
                If ctrl.ControlFormat.Value = 1 Then nohide = Array("Sheet1")    
        End Select
    End If
Next ctrl

Then loop through the sheets to hide based on the visible-list:

If Not IsEmpty(nohide) Then
    Dim ws As Worksheet
    For Each ws In Worksheets
        If IsError(Application.Match(ws.Name, nohide, 0)) Then
            ws.Visible = False
        Else
            ws.Visible = True
        End If
    Next ws
    nohide = ""
End If

---

Edit1:

Ensuring you're using the form control, not the activeX or are on a userform... here is how you select the option buttons that are associated with the above syntax:

Related to having one macro, the purpose was to utilize a switch, Select Case, in vba... a switch works that each scenario of a switch is verified and where one is met it is carried out. for VBA, you are Selecting which Case is to be utilized. Here is an example for multiple buttons:

    Select Case ctrl.Name
        Case "Option Button 1"
            If ctrl.ControlFormat.Value = 1 Then nohide = Array("Sheet1") 
        Case "Option Button 2"
            If ctrl.ControlFormat.Value = 1 Then nohide = Array("Sheet1","Sheet2") 
    End Select

Note that EVERY case will be assessed, so when you get to the next ctrl in the sheet, it might be Option Button 2, so that case will be assessed, but the if-statement will fail and nohide will still be what was assigned in the first case, which was true.