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.

 

[Domenic]

Make sure that the caption for your checkbox and the sheet name on your sheet tab are spelled correctly, and don't contain any extra spaces . . .

 

[Sektor]

As as side note, code could trimmed down to:

Sheets(CheckBox2.Caption).Visible = IIf(Sheets(CheckBox2.Caption).Visible = xlSheetHidden, xlSheetVisible, xlSheetHidden)
' or:
With Sheets(CheckBox2.Caption)
  .Visible = IIf(.Visible = xlSheetHidden, xlSheetVisible, xlSheetHidden)
End With

 

[Skyybot]

Try this:

Private Sub CheckBox2_Click()
If Me.CheckBox2.Value = True Then Sheets(Me.CheckBox2.Caption).Visible = False
If Me.CheckBox2.Value = False Then Sheets(Me.CheckBox2.Caption.Visible = True
End Sub

 

[Skyybot]

Here's a cool little thing I came up with. If you put this code in your first Sheet in the VBA Project, put "Hide/UnHide" in cell A1 of Sheet 1, and run the Hide_Sheets macro it will create Checkboxes with Sheet names and write the Hide/Unhide code for you. It won't Hide Sheet1 obviously.

Option Explicit
Option Base 1

Sub Hide_Sheets()
Dim shtcnt As Integer, i As Integer
shtcnt = ThisWorkbook.Sheets.Count
For i = 2 To shtcnt
    Me.OLEObjects.Add ClassType:="Forms.Checkbox.1", Top:=Cells(i, 1).Top, _
        Left:=Cells(i, 1).Left, Height:=Cells(i, 1).Height, Width:=Cells(i, 1).Width
    With Me.OLEObjects(i - 1)
        .Name = Sheets(i).Name & "CheckBox"
        .Object.Caption = Sheets(i).Name
    End With
    cpcnt Me.OLEObjects(i - 1).Name, Sheets(i).Name
Next i
Sheets(1).Activate
End Sub
Sub cpcnt(nm As String, sht As String)
Dim cp, str As String
str = "Sub " & nm & "_Click()" & vbCrLf _
    & "If Me." & nm & " = True Then " & sht & ".Visible = False" & vbCrLf _
    & "If Me." & nm & " = False Then " & sht & ".Visible = True" & vbCrLf _
    & "End Sub"
cp = Application.VBE.VBProjects("VBAProject") _
    .VBComponents("Sheet1").CodeModule.CountOfLines
Application.VBE.VBProjects("VBAProject") _
    .VBComponents("Sheet1").CodeModule.InsertLines cp, str
End Sub

 

[SuziMacca]

Try this:

Private Sub CheckBox2_Click()
If Me.CheckBox2.Value = True Then Sheets(Me.CheckBox2.Caption).Visible = False
If Me.CheckBox2.Value = False Then Sheets(Me.CheckBox2.Caption.Visible = True
End Sub

Thank you. Tried that & comes up a Run-time error '9'. subscript out of range.

 

[SuziMacca]

Make sure that the caption for your checkbox and the sheet name on your sheet tab are spelled correctly, and don't contain any extra spaces . . .

Thank you, double checked that & corrected.

 

[SuziMacca]

As as side note, code could trimmed down to:

Sheets(CheckBox2.Caption).Visible = IIf(Sheets(CheckBox2.Caption).Visible = xlSheetHidden, xlSheetVisible, xlSheetHidden)
' or:
With Sheets(CheckBox2.Caption)
  .Visible = IIf(.Visible = xlSheetHidden, xlSheetVisible, xlSheetHidden)
End With

Thank you. Tried that one & comes up a Run time error '9'. Subscript out of range.

 

[Skyybot]

Thank you. Tried that & comes up a Run-time error '9'. subscript out of range.

Did you try my solution in Post#5?

 

[SuziMacca]

Did you try my solution in Post#5?

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?