VBA code to use checkboxes to hide sheets.

[SuziMacca]

Hi, I am quite new to VBA code so am hoping someone can help. I am trying to use individual check boxes to hide sheets/multiple sheets when ticked. The first I set up works & hides/unhides the relevant sheet, but when I add a new checkbox, if wont hide the 2nd sheet. I am looking at doing this using multiple checkboxes linked to individual sheets. If each check box is ticked, I want all sheets hidden & then unhidden by unchecking.
These are the sheet names & I have set up 2 check boxes so far - capacity works however individual 1 doesn't.

The sheets I want to hide:

Capacity

Individual 1

Individual 2

Entity 1 Financials

Entity 2 Financials

Entity 3 Financials

Entity 4 Financials

This is the code I have entered:
Private Sub CheckBox1_Click()
If Sheets(CheckBox1.Caption).Visible = xlHidden Then
Sheets(CheckBox1.Caption).Visible = xlSheetVisible
Else
Sheets(CheckBox1.Caption).Visible = xlHidden
End If

End Sub

Private Sub CheckBox2_Click()
If Sheets(CheckBox2.Caption).Visible = xlHidden Then
Sheets(CheckBox2.Caption).Visible = xlSheetVisible
Else
Sheets(CheckBox2.Caption).Visible = xlHidden
End If
End Sub

This is the error message that comes up when I check individual 1:

Run-time error '9'. Subscript out of range.

As mentioned, very new to this, so any assistance would be greatly appreciated.
Thanks in advance.

 

[Skyybot]

I attempted to & can see where it writes the code & the check box. I am very new at this so will take me a little while to work on this one & wrap my very amateur brain around it. Can I change the checkbox from A1 to say Q2? If I do this do I have to change anything in your code?

Yes. And yes.

 

[jolivanes]

If you want to try something totally different. No check boxes messing up your sheet.

Paste the code in a regular module, put a form control button on your sheet and assign the code to the button.
The sheets you select in the list box will be made hidden if the sheets are visible and made visible if they are hidden.

Sub CreateUserForm_2()

Application.VBE.MainWindow.Visible = False
Dim myForm As Object
Dim NewListBox As Object
Dim lbl As Object
Dim cmd As Object

Set myForm = ThisWorkbook.VBProject.VBComponents.Add(3)

With myForm
    .Properties("Caption") = "Select Sheet To Hide/UnHide"
    .Properties("Width") = 175    
    .Properties("Height") = 275    
End With

Set lbl = myForm.designer.Controls.Add("Forms.Label.1", "lbl", True)
With lbl
    .Top = 6
    .Left = 10
    .Height = 24
    .Width = 150
    .Caption = "Hide or UnHide Selected Sheets."
    .Font.Name = "Tahoma"
    .Font.Bold = True
    .Font.Size = 10
    .TextAlign = 2
End With

Set NewListBox = myForm.designer.Controls.Add("Forms.listbox.1")
With NewListBox
    .Name = "ListBox1"
    .Top = 35    '10
    .Left = 10
    .Width = 150
    .Height = 180    '230
    .Font.Size = 8
    .MultiSelect = 1
    .Font.Name = "Tahoma"
End With

Set cmd = myForm.designer.Controls.Add("Forms.CommandButton.1", "cmd1", True)
With cmd
    .Left = 10
    .Top = 225
    .Width = 150
    .Height = 24
    .Caption = "Hide/Unhide Selected"
    .Font.Name = "Tahoma"
    .Font.Bold = True
    .Font.Size = 10
End With

myForm.CodeModule.insertlines 2, "Private Sub UserForm_Initialize()"
myForm.CodeModule.insertlines 3, "Dim i As Long, dat"
myForm.CodeModule.insertlines 4, "    For i = 2 To ThisWorkbook.Worksheets.Count"
myForm.CodeModule.insertlines 5, "        dat = dat & ""|"" & Sheets(i).Name"
myForm.CodeModule.insertlines 6, "    Next i"
myForm.CodeModule.insertlines 7, "dat = Split(Mid(dat, 2), ""|"")"
myForm.CodeModule.insertlines 8, "ListBox1.List = Application.Transpose(dat)"
myForm.CodeModule.insertlines 9, "End Sub"

myForm.CodeModule.insertlines 11, "Private Sub cmd1_Click()"
myForm.CodeModule.insertlines 12, "Dim i As Long"
myForm.CodeModule.insertlines 13, "    With ListBox1"
myForm.CodeModule.insertlines 14, "        For i = 0 To .ListCount - 1"
myForm.CodeModule.insertlines 15, "            If .Selected(i) Then"
myForm.CodeModule.insertlines 16, "                If Worksheets(.List(i)).Visible = True Then"
myForm.CodeModule.insertlines 17, "                    Worksheets(.List(i)).Visible = xlSheetHidden"
myForm.CodeModule.insertlines 18, "                        Else"
myForm.CodeModule.insertlines 19, "                    Worksheets(.List(i)).Visible = xlSheetVisible"
myForm.CodeModule.insertlines 20, "                End If"
myForm.CodeModule.insertlines 21, "            End If"
myForm.CodeModule.insertlines 22, "        Next i"
myForm.CodeModule.insertlines 23, "    End With"
myForm.CodeModule.insertlines 24, "Unload Me"
myForm.CodeModule.insertlines 25, "End Sub"

VBA.UserForms.Add(myForm.Name).Show
'Delete the form (Optional)
ThisWorkbook.VBProject.VBComponents.Remove myForm
End Sub

 

[jolivanes]

In the code from Post #12, Line 11 to Line 25 should be replaced by following.

myForm.CodeModule.insertlines 11, "Private Sub cmd1_Click()"
myForm.CodeModule.insertlines 12, "Dim i As Long"
myForm.CodeModule.insertlines 13, "    With ListBox1"
myForm.CodeModule.insertlines 14, "        For i = 0 To .ListCount - 1"
myForm.CodeModule.insertlines 15, "            If .Selected(i) Then Worksheets(.List(i)).Visible = Not Worksheets(.List(i)).Visible"
myForm.CodeModule.insertlines 16, "        Next i"
myForm.CodeModule.insertlines 17, "    End With"
myForm.CodeModule.insertlines 18, "Unload Me"
myForm.CodeModule.insertlines 19, "End Sub"

 

[SuziMacca]

Thank you everyone. All solutions have worked. Rookie error - additional sheets were not unprotected.

 

[Joe4]

SuziMacca,

Please Note: When marking a post as the solution, please mark the post that contains the solution (not your own post acknowledging that some other post was the solution).
When a post is marked as the solution, it is then shown right underneath the original question so people viewing the question can easily see the question and solution in a single quick glance without having to hunt through all the posts.