VBA to perform multiple actions depending on checkbox clicks

[brunette]

Hi everyone,

I have various sheets in one workbook. Sheet 1 is the "summary sheet" where I have all my checkboxes. What I am after is, depending on what checkboxes are ticked, certain actions should happen. For each checkbox, I need 2 actions to occur simultaneously. I need a sheet unhidden and, on another sheet, certain rows unhidden (in both cases the default setting is hidden).

This leads me to ask 2 questions:

1) The code I have managed to piece together only works in part, insofar that I can get it to unhide the sheet I want but it isn't unhiding the rows I want on the second sheet. Can you help me as I can't see where I'm going wrong.
2) Will this code work if I select multiple checkboxes at the same time?

The checkbox is ActiveX

Private Sub CheckBox1_Click()
    On Error Resume Next
    ThisWorkbook.Sheets("Sheet2").Visible = CheckBox1.Value
    If CheckBox1 = True Then
    ThisWorkbook.Sheets("Sheet3").Select
[5:10].EntireRow.Hidden = False
Else: [5:10].EntireRow.Hidden = True
End If
       
End Sub

Thanks for your assistance
B

 

[JackDanIce]

Untested, try:

Private Sub CheckBox1_Click()
      
    On Error Resume Next
    Sheets("Sheet2").Visible = IIf(CheckBox1, xlVisible, xlSheetHidden)
    Sheets("Sheet3").Cells(5, 1).Resize(6).EntireRow.Hidden = Not CheckBox1
    On Error GoTo 0
   
End Sub

If this works, then to generalise for all checkboxes, (also untested) try:

Private Sub CheckBox1_Click()

    Click_CheckBox CheckBox1

End Sub

Public Sub Click_CheckBox(ByRef ChkBox As Variant)
'This can reside in a module
    On Error Resume Next
    Sheets("Sheet2").Visible = IIf(ChkBox, xlVisible, xlSheetHidden)
    Sheets("Sheet3").Cells(5, 1).Resize(6).EntireRow.Hidden = Not ChkBox
    On Error GoTo 0

End Sub

Unsure if code will work for multiple boxes, try and see!

 

[brunette]

Hi JackDanIce,

Thanks so much for this.

It worked in part but I managed to tweak it so it works exactly the way I want it, so thank you!

Here's what I had to change for it to work properly:

Private Sub CheckBox1_Click()
      
    On Error Resume Next
    ThisWorkbook.Sheets("Sheet1").Visible = CheckBox1.Value
    Sheets("Sheet3").Cells(5, 1).Resize(6).EntireRow.Hidden = Not CheckBox1
    On Error GoTo 0
   
End Sub

From here, the rest worked perfectly too. Thanks again.

 

[JackDanIce]

You got it to work, even better and happy days!

PS you can probably drop the ThisWorkbook part, you don't pre-fix to Sheets("Sheets3"), which I assume is in the same workbook as Thisworkbook and your code works...