Hide rows according to checkboxes on seperate sheet

[rhsen]

Hi,

I'm new to VBA and wanted to try to have a bit of fun first.

I have two sheets, called Settings and Spell_List (It is for DnD).
In Settings I can cross 3 check boxes, which depending on the selected check box, will hide certain rows in the other sheet.

Sub MoonPhase()
 If Worksheets("Settings").Range("P7").Value = "TRUE" Then
            Worksheets("Spell_List").Rows("13:14").EntireRow.Hidden = True
            Worksheets("Spell_List").Rows("20:21").EntireRow.Hidden = True
            Worksheets("Spell_List").Rows("25:26").EntireRow.Hidden = True
            Worksheets("Spell_List").Rows("31:32").EntireRow.Hidden = True
    ElseIf Worksheets("Settings").Range("P9").Value = "TRUE" Then
            Worksheets("Spell_List").Rows("12,14").EntireRow.Hidden = True
            Worksheets("Spell_List").Rows("19,21").EntireRow.Hidden = True
            Worksheets("Spell_List").Rows("24,26").EntireRow.Hidden = True
            Worksheets("Spell_List").Rows("30,32").EntireRow.Hidden = True
    ElseIf Worksheets("Settings").Range("P11").Value = "TRUE" Then
            Worksheets("Spell_List").Rows("12:13").EntireRow.Hidden = True
            Worksheets("Spell_List").Rows("19:20").EntireRow.Hidden = True
            Worksheets("Spell_List").Rows("24:25").EntireRow.Hidden = True
            Worksheets("Spell_List").Rows("30,31").EntireRow.Hidden = True
End If
End Sub

This is what i have so far. If I cross off box P7, then it should hide according to above. And different rows if the other are checked.
But nothing happens at the moment.

I'm missing something here, I just don't know what.

Rune

 

[Logit]

Example, paste in the Sheet level module. In this example, paste in the Sheet1 Module :

Option Explicit

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
    Worksheets("Sheet2").Rows("13:14").EntireRow.Hidden = True
            Worksheets("Sheet2").Rows("20:21").EntireRow.Hidden = True
            Worksheets("Sheet2").Rows("25:26").EntireRow.Hidden = True
            Worksheets("Sheet2").Rows("31:32").EntireRow.Hidden = True
Else
    Worksheets("Sheet2").Rows("13:14").EntireRow.Hidden = False
            Worksheets("Sheet2").Rows("20:21").EntireRow.Hidden = False
            Worksheets("Sheet2").Rows("25:26").EntireRow.Hidden = False
            Worksheets("Sheet2").Rows("31:32").EntireRow.Hidden = False
End If
End Sub

Also, utilize the ACTIVEX checkbox control.

 

[John_w]

In Settings I can cross 3 check boxes, which depending on the selected check box, will hide certain rows in the other sheet.

Assuming the 3 check boxes are Form Control check boxes (not ActiveX), and their linked cells are P7, P9 and P11. Clicking a check box will put TRUE or FALSE in the linked cell - these are Boolean values, not Strings ("TRUE"/"FALSE"). You must assign a macro to each check box if you want your code to run when you click a check box.

Therefore assign the MoonPhase macro to the 3 checkboxes.

However, the If ... ElseIf ... ElseIf logic of your code means that only 1 of the check boxes is actually checked and only 1 set of rows is hidden if that check box's linked cell is TRUE. Your code doesn't unhide rows if the linked cell is FALSE.

Try this code instead to hide/unhide the rows for all 3 check boxes:

Sub MoonPhase()
    With Worksheets("Spell_List")
        Union(.Rows("13:14"), .Rows("20:21"), .Rows("25:26"), .Rows("31:32")).EntireRow.Hidden = Worksheets("Settings").Range("P7").Value
        Union(.Rows("12"), .Rows("14"), .Rows("19"), .Rows("21"), .Rows("24").Rows("26").Rows("30").Rows("32")).EntireRow.Hidden = Worksheets("Settings").Range("P9").Value
        Union(.Rows("12:13"), .Rows("19:20"), .Rows("24:25"), .Rows("30:31")).EntireRow.Hidden = Worksheets("Settings").Range("P11").Value
    End With
End Sub

 

[rhsen]

Union(.Rows("13:14"), .Rows("20:21"), .Rows("25:26"), .Rows("31:32")).EntireRow.Hidden = Worksheets("Settings").Range("P7").Value
Union(.Rows("12"), .Rows("14"), .Rows("19"), .Rows("21"), .Rows("24").Rows("26").Rows("30").Rows("32")).EntireRow.Hidden = Worksheets("Settings").Range("P9").Value
Union(.Rows("12:13"), .Rows("19:20"), .Rows("24:25"), .Rows("30:31")).EntireRow.Hidden = Worksheets("Settings").Range("P11").Value

It seems to work, but only the 3rd check box works fully.
1st doesn't hide any of the others and 2nd only hides 1 of the others.

 

[John_w]

It seems to work, but only the 3rd check box works fully.

Because the code looks at the 3rd check box last, overriding any rows common to it and the other check boxes, which where hidden or shown by the 1st and 2nd check boxes.

Would option buttons instead of check boxes be more appropriate for what you want? They are used to select only one of multiple options.

 

[rhsen]

Because the code looks at the 3rd check box last, overriding any rows common to it and the other check boxes, which where hidden or shown by the 1st and 2nd check boxes.

Would option buttons instead of check boxes be more appropriate for what you want? They are used to select only one of multiple options.

Yes, they would. I have changed them now and added the following to each:

Sub MoonPhase()
    With Worksheets("Spell_List")
        Union(.Rows("13:14"), .Rows("20:21"), .Rows("25:26"), .Rows("31:32")).EntireRow.Hidden = Worksheets("Settings").Range("P11").Value = 1
        Union(.Rows("12"), .Rows("14"), .Rows("19"), .Rows("21"), .Rows("24"), .Rows("26"), .Rows("30"), .Rows("32")).EntireRow.Hidden = Worksheets("Settings").Range("P11").Value = 2
        Union(.Rows("12:13"), .Rows("19:20"), .Rows("24:25"), .Rows("30:31")).EntireRow.Hidden = Worksheets("Settings").Range("P11").Value = 3
    End With
End Sub

But 3rd option button still overrides.

 

[John_w]

Show all the rows first then hide the rows depending on the selected option button:

Sub MoonPhase()
    With Worksheets("Spell_List")
       Union(.Rows("13:14"), .Rows("20:21"), .Rows("25:26"), .Rows("31:32")).EntireRow.Hidden = False
       Union(.Rows("12"), .Rows("14"), .Rows("19"), .Rows("21"), .Rows("24").Rows("26").Rows("30").Rows("32")).EntireRow.Hidden = False
       Union(.Rows("12:13"), .Rows("19:20"), .Rows("24:25"), .Rows("30:31")).EntireRow.Hidden = False
       Select Case Worksheets("Settings").Range("P11").Value
            Case 1: Union(.Rows("13:14"), .Rows("20:21"), .Rows("25:26"), .Rows("31:32")).EntireRow.Hidden = True
            Case 2: Union(.Rows("12"), .Rows("14"), .Rows("19"), .Rows("21"), .Rows("24").Rows("26").Rows("30").Rows("32")).EntireRow.Hidden = True
            Case 3: Union(.Rows("12:13"), .Rows("19:20"), .Rows("24:25"), .Rows("30:31")).EntireRow.Hidden = True
        End Select
    End With
End Sub

 

[rhsen]

Show all the rows first then hide the rows depending on the selected option button:

Sub MoonPhase()
    With Worksheets("Spell_List")
       Union(.Rows("13:14"), .Rows("20:21"), .Rows("25:26"), .Rows("31:32")).EntireRow.Hidden = False
       Union(.Rows("12"), .Rows("14"), .Rows("19"), .Rows("21"), .Rows("24").Rows("26").Rows("30").Rows("32")).EntireRow.Hidden = False
       Union(.Rows("12:13"), .Rows("19:20"), .Rows("24:25"), .Rows("30:31")).EntireRow.Hidden = False
       Select Case Worksheets("Settings").Range("P11").Value
            Case 1: Union(.Rows("13:14"), .Rows("20:21"), .Rows("25:26"), .Rows("31:32")).EntireRow.Hidden = True
            Case 2: Union(.Rows("12"), .Rows("14"), .Rows("19"), .Rows("21"), .Rows("24").Rows("26").Rows("30").Rows("32")).EntireRow.Hidden = True
            Case 3: Union(.Rows("12:13"), .Rows("19:20"), .Rows("24:25"), .Rows("30:31")).EntireRow.Hidden = True
        End Select
    End With
End Sub

Thank you very much! I can see that I was nowhere close to understanding anything yet.
I needed only to add some commas and spaces in the second case and it worked perfectly.

 

[John_w]

Pleased you got it working. Don't know why I missed the missing commas for the 2nd case. Here's the corrected code:

Sub MoonPhase()
    With Worksheets("Spell_List")
        Union(.Rows("13:14"), .Rows("20:21"), .Rows("25:26"), .Rows("31:32")).EntireRow.Hidden = False
        Union(.Rows("12"), .Rows("14"), .Rows("19"), .Rows("21"), .Rows("24"), .Rows("26"), .Rows("30"), .Rows("32")).EntireRow.Hidden = False
        Union(.Rows("12:13"), .Rows("19:20"), .Rows("24:25"), .Rows("30:31")).EntireRow.Hidden = False
        Select Case Worksheets("Settings").Range("P6").Value
            Case 1: Union(.Rows("13:14"), .Rows("20:21"), .Rows("25:26"), .Rows("31:32")).EntireRow.Hidden = True
            Case 2: Union(.Rows("12"), .Rows("14"), .Rows("19"), .Rows("21"), .Rows("24"), .Rows("26"), .Rows("30"), .Rows("32")).EntireRow.Hidden = True
            Case 3: Union(.Rows("12:13"), .Rows("19:20"), .Rows("24:25"), .Rows("30:31")).EntireRow.Hidden = True
        End Select
    End With
End Sub