excel forms click

[lac]

I want to create a form that will allow users to check a box, if the box is checked I want it to hide a sheet, if it's uncheck then it unhides the sheet. how do I do this when craeting a forms control?



Private Sub CheckBox1_Click()
' Sub HideSheets()
ActiveSheet.Unprotect Password:="ANM"
Sheets("Post Review Data").Visible = False

End If
Private Sub CheckBox1_UnClick()

' Sub UnHideSheets()
ActiveSheet.Unprotect Password:="ANM"
Sheets("Post Review Data").Visible = True

End Sub

 

[Greg Truby]

Each checkbox has its own event handling procedure. So you end up coding for each checkbox. Note that the "all" checkbox does not toggle the sheets' visibilty directly. Rather, we manipulate the other three checkboxes' values and let those checkboxes' event handlers process the visibility changes. This helps keep the values of the controls in sync with the sheets' visibility statuses as we run about changing things.

Please be aware that in production I would NEVER name my controls like this. I would follow my naming procotol as I describe here. I am leaving the default names here because they may be more familiar to you.

Private Sub CheckBox1_Click()
    Sheet1.Visible = Me.CheckBox1.Value
End Sub
Private Sub CheckBox2_Click()
    Sheet2.Visible = Me.CheckBox2.Value
End Sub
Private Sub CheckBox3_Click()
    Sheet3.Visible = Me.CheckBox3.Value
End Sub
Private Sub CheckBox4_Click()
    '// does all other checkboxes
    With Me
        .CheckBox1.Value = .CheckBox4.Value
        .CheckBox2.Value = .CheckBox4.Value
        .CheckBox3.Value = .CheckBox4.Value
    End With
End Sub
 
Private Sub UserForm_Initialize()
    If Sheet1.Visible = xlSheetVeryHidden Then
        Me.CheckBox1.Enabled = False
    Else
        Me.CheckBox1.Enabled = True
        Me.CheckBox1.Value = Sheet1.Visible
    End If
 
    If Sheet2.Visible = xlSheetVeryHidden Then
        Me.CheckBox2.Enabled = False
    Else
        Me.CheckBox2.Enabled = True
        Me.CheckBox2.Value = Sheet2.Visible
    End If
 
    If Sheet3.Visible = xlSheetVeryHidden Then
        Me.CheckBox3.Enabled = False
    Else
        Me.CheckBox3.Enabled = True
        Me.CheckBox3.Value = Sheet3.Visible
    End If
 
    '// checkbox 4 is all
    Me.CheckBox4.Enabled = Me.CheckBox1.Enabled _
                       And Me.CheckBox2.Enabled _
                       And Me.CheckBox3.Enabled
 
    If Me.CheckBox4.Enabled Then
        Me.CheckBox4.Value = Me.CheckBox1.Value _
                         And Me.CheckBox2.Value _
                         And Me.CheckBox3.Value
 
    End If
End Sub