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

 

[RSXchin]

Something like this?

Private Sub CheckBox1_Click()
    If Me.CheckBox1 = True Then
        ActiveSheet.Unprotect Password:="ANM"
        Sheets("Post Review Data").Visible = False
    Else
        ActiveSheet.Unprotect Password:="ANM"
        Sheets("Post Review Data").Visible = True
    End If
End Sub

 

[Smitty]

Welcome to the Board!

You can do it in one line:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox1_Click()<br>    Sheets("Post Review Data").Visible = <SPAN style="color:#00007F">Not</SPAN> Sheets("Post Review Data").Visible<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[Greg Truby]

Hiya, Smitty! 'zup? BTW, Smitty and I both assume you'll know to put the password schtuff in the code. Also do be aware that - depending on how important your password's protection is - you may want to password protect your VBA project if your code has the passwords hardcoded into it.

Personally, I prefer to read the box's value in the setting process - something like:

Private Sub CheckBox1_Click()
    Sheet2.Visible = Me.CheckBox1.Value
End Sub

Note that any of this sort of implies that you do a proper job of initializing the userform's checkboxes' values...

Private Sub UserForm_Initialize()
    If Sheet2.Visible = xlSheetVeryHidden Then
        Me.CheckBox1.Enabled = False
    Else
        Me.CheckBox1.Enabled = True
        Me.CheckBox1.Value = Sheet2.Visible
    End If
End Sub

 

[Greg Truby]

Also - you may want to note that a worksheet's VISIBLE property is not a boolean per se. It's a long, with possible values of xlSheetHidden, xlSheetVisible & xlSheetVeryHidden. However MS was kind enough to set xlSheetHidden to 0, which = FALSE and xlSheetVisible = -1 = TRUE. [In VBA, TRUE = -1, while in a worksheet cell TRUE = 1]

 

[Smitty]

Personally, I prefer to read the boxes value in the setting process - something like:

Private Sub CheckBox1_Click()
    Sheet2.Visible = Me.CheckBox1.Value
End Sub

Edit to my first post: you can do it in one line, but even shorter.

Heya Greg! How's the heat? (It's a balmy 65 here - )

 

[Greg Truby]

Heya Greg! How's the heat? (It's a balmy 65 here - )

Hotter'n a snake's belly in a wagon rut. Next five days' forecast = 98°, 98°, 99°, 99° & 100°. I think central Kansas hit 110° today.

 

[lac]

Something like this?

Private Sub CheckBox1_Click()
    If Me.CheckBox1 = True Then
        ActiveSheet.Unprotect Password:="ANM"
        Sheets("Post Review Data").Visible = False
    Else
        ActiveSheet.Unprotect Password:="ANM"
        Sheets("Post Review Data").Visible = True
    End If
End Sub

Thank you- so what if I have two check boxes that will allow users to click either options. For example box1 allows users to show only sheet 1 and 2 but hides sheet 3, but check box2 allows users to show sheet 3, how can I combine the formula so that a user is allow to click both boxes to show sheets 1,2, and 3? I hope this makes sense?

 

[Greg Truby]

Before we get to that -- is really as complex as you see this going? Just two checkboxes? Or is there a chance you might end up with a half-dozen checkboxes or even more, managing the visibility of various sheets? Because if it's one or two then we can stick with this simplistic architecture. If not, we can switch to an architecture that's more complex, but much more scalable and manageable.

 

[lac]

there's going to be a total of 4 boxes, users have the option to check 3 of the 4 box because the 4th box will say ALL.