Share via

Hiding rows with Checkboxes is only working if I check the boxes in order.

Anonymous
2020-10-09T22:54:45+00:00

Hello, 

I have been having a hard time finding an answer to this puzzle.  

I created 5 Active x controls checkboxes that are supposed to hide certain rows in each of 9 sheets.  I am creating a shell for a sheet function so I'm trying to get it to function first so that is why they are all currently the same range  for each checkbox.

If I uncheck or check the boxes in order 1,2,3,4,5  They hide/unhide the rows non problem.  

If I do not uncheck or check the boxes in order 3,1,2,5,4.  The rows do nothing.

Does anyone have an insight of why this might be happening? Any help is much appreciated.

Below is the full set of macros for checkboxes 1-5.

Private Sub CheckBox1_Click()

If CheckBox1.Value = False Then

Sheet3.[4:8].EntireRow.Hidden = True

Sheet4.[4:8].EntireRow.Hidden = True

Sheet5.[4:8].EntireRow.Hidden = True

Sheet6.[4:8].EntireRow.Hidden = True

Sheet7.[4:8].EntireRow.Hidden = True

Sheet8.[4:8].EntireRow.Hidden = True

Sheet9.[4:8].EntireRow.Hidden = True

Sheet10.[4:8].EntireRow.Hidden = True

Else

Sheet3.[4:8].EntireRow.Hidden = False

Sheet4.[4:8].EntireRow.Hidden = False

Sheet5.[4:8].EntireRow.Hidden = False

Sheet6.[4:8].EntireRow.Hidden = False

Sheet7.[4:8].EntireRow.Hidden = False

Sheet8.[4:8].EntireRow.Hidden = False

Sheet9.[4:8].EntireRow.Hidden = False

Sheet10.[4:8].EntireRow.Hidden = False

End If

End Sub

Private Sub CheckBox2_Click()

If CheckBox1.Value = False Then

Sheet3.[9:12].EntireRow.Hidden = True

Sheet4.[9:12].EntireRow.Hidden = True

Sheet5.[9:12].EntireRow.Hidden = True

Sheet6.[9:12].EntireRow.Hidden = True

Sheet7.[9:12].EntireRow.Hidden = True

Sheet8.[9:12].EntireRow.Hidden = True

Sheet9.[9:12].EntireRow.Hidden = True

Sheet10.[9:12].EntireRow.Hidden = True

Else

Sheet3.[9:12].EntireRow.Hidden = False

Sheet4.[9:12].EntireRow.Hidden = False

Sheet5.[9:12].EntireRow.Hidden = False

Sheet6.[9:12].EntireRow.Hidden = False

Sheet7.[9:12].EntireRow.Hidden = False

Sheet8.[9:12].EntireRow.Hidden = False

Sheet9.[9:12].EntireRow.Hidden = False

Sheet10.[9:12].EntireRow.Hidden = False

End If

End Sub

Private Sub CheckBox3_Click()

If CheckBox1.Value = False Then

Sheet3.[13:16].EntireRow.Hidden = True

Sheet4.[13:16].EntireRow.Hidden = True

Sheet5.[13:16].EntireRow.Hidden = True

Sheet6.[13:16].EntireRow.Hidden = True

Sheet7.[13:16].EntireRow.Hidden = True

Sheet8.[13:16].EntireRow.Hidden = True

Sheet9.[13:16].EntireRow.Hidden = True

Sheet10.[13:16].EntireRow.Hidden = True

Else

Sheet3.[13:16].EntireRow.Hidden = False

Sheet4.[13:16].EntireRow.Hidden = False

Sheet5.[13:16].EntireRow.Hidden = False

Sheet6.[13:16].EntireRow.Hidden = False

Sheet7.[13:16].EntireRow.Hidden = False

Sheet8.[13:16].EntireRow.Hidden = False

Sheet9.[13:16].EntireRow.Hidden = False

Sheet10.[13:16].EntireRow.Hidden = False

End If

End Sub

Private Sub CheckBox4_Click()

If CheckBox1.Value = False Then

Sheet3.[16:19].EntireRow.Hidden = True

Sheet4.[16:19].EntireRow.Hidden = True

Sheet5.[16:19].EntireRow.Hidden = True

Sheet6.[16:19].EntireRow.Hidden = True

Sheet7.[16:19].EntireRow.Hidden = True

Sheet8.[16:19].EntireRow.Hidden = True

Sheet9.[16:19].EntireRow.Hidden = True

Sheet10.[16:19].EntireRow.Hidden = True

Else

Sheet3.[16:19].EntireRow.Hidden = False

Sheet4.[16:19].EntireRow.Hidden = False

Sheet5.[16:19].EntireRow.Hidden = False

Sheet6.[16:19].EntireRow.Hidden = False

Sheet7.[16:19].EntireRow.Hidden = False

Sheet8.[16:19].EntireRow.Hidden = False

Sheet9.[16:19].EntireRow.Hidden = False

Sheet10.[16:19].EntireRow.Hidden = False

End If

End Sub

Private Sub CheckBox5_Click()

If CheckBox1.Value = False Then

Sheet3.[20:24].EntireRow.Hidden = True

Sheet4.[20:24].EntireRow.Hidden = True

Sheet5.[20:24].EntireRow.Hidden = True

Sheet6.[20:24].EntireRow.Hidden = True

Sheet7.[20:24].EntireRow.Hidden = True

Sheet8.[20:24].EntireRow.Hidden = True

Sheet9.[20:24].EntireRow.Hidden = True

Sheet10.[20:24].EntireRow.Hidden = True

Else

Sheet3.[20:24].EntireRow.Hidden = False

Sheet4.[20:24].EntireRow.Hidden = False

Sheet5.[20:24].EntireRow.Hidden = False

Sheet6.[20:24].EntireRow.Hidden = False

Sheet7.[20:24].EntireRow.Hidden = False

Sheet8.[20:24].EntireRow.Hidden = False

Sheet9.[20:24].EntireRow.Hidden = False

Sheet10.[20:24].EntireRow.Hidden = False

End If

End Sub

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

OssieMac 48,001 Reputation points Volunteer Moderator
2020-10-10T03:29:47+00:00

With every CheckBox sub you are testing the value of CheckBox1. Is this correct or should it test the value of the CheckBox that called the sub.

With CheckBoxes 2 to 5 it does not matter whether they are checked or unchecked, the called code tests the value of CheckBox1 and sets the hidden or visible based on the CheckBox1 value.

Was this answer helpful?

1 person found this answer helpful.
0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. OssieMac 48,001 Reputation points Volunteer Moderator
    2020-10-10T04:25:48+00:00

    I can't believe I missed that and it totally makes sense. 

    I was blindly copy/pasting the formula from checkbox 1 into the rest of the the Subs. 

    We've all made those mistakes. Show me someone who has never made a mistake and I'll show you someone who has never done anything.

    I also notice that row 16 overlaps. You have it in CheckBox3 and CheckBox4 but it might be what you intended.

    Just a little more info you might like. Because the CheckBox is either True of False you can use that to set the hidden row. In your code it is the opposite of the value of the CheckBox. eg you can use = Not CheckBox1.Value so that it sets it opposite to the CheckBox value and you don't need to use Else in the If/End If code.

    Plus the following code creates an array of the worksheets and then loops through the worksheets and sets the rows to Hidden/UnHidden based on the opposite value of the CheckBox.

    The code actually uses the CodeName of the worksheet to get the User given name for use in the array. This is not a problem because the Codename is the preferred method of programming but it gets the given name at the time the code is run so if the User changes the name then it will not be a problem. Unfortunately I don't know of any way of assigning the CodeNames to an array and then reference them in the code.

    Private Sub CheckBox1_Click()

        Dim arrShts() As Variant

        Dim i As Long

        arrShts() = Array(Sheet3.Name, Sheet4.Name, Sheet5.Name, Sheet6.Name, Sheet7.Name, Sheet8.Name, Sheet9.Name, Sheet10.Name)

        For i = LBound(arrShts) To UBound(arrShts)

            With Worksheets(arrShts(i))

                .[4:8].EntireRow.Hidden = Not CheckBox1.Value

            End With

        Next i

    End Sub

    Private Sub CheckBox2_Click()

        Dim arrShts() As Variant

        Dim i As Long

        arrShts() = Array(Sheet3.Name, Sheet4.Name, Sheet5.Name, Sheet6.Name, Sheet7.Name, Sheet8.Name, Sheet9.Name, Sheet10.Name)

        For i = LBound(arrShts) To UBound(arrShts)

            With Worksheets(arrShts(i))

                .[9:12].EntireRow.Hidden = Not CheckBox2.Value

            End With

        Next i

    End Sub

    Private Sub CheckBox3_Click()

        Dim arrShts() As Variant

        Dim i As Long

        arrShts() = Array(Sheet3.Name, Sheet4.Name, Sheet5.Name, Sheet6.Name, Sheet7.Name, Sheet8.Name, Sheet9.Name, Sheet10.Name)

        For i = LBound(arrShts) To UBound(arrShts)

            With Worksheets(arrShts(i))

                .[13:16].EntireRow.Hidden = Not CheckBox3.Value

            End With

        Next i

    End Sub

    Private Sub CheckBox4_Click()

        Dim arrShts() As Variant

        Dim i As Long

        arrShts() = Array(Sheet3.Name, Sheet4.Name, Sheet5.Name, Sheet6.Name, Sheet7.Name, Sheet8.Name, Sheet9.Name, Sheet10.Name)

        For i = LBound(arrShts) To UBound(arrShts)

            With Worksheets(arrShts(i))

                .[16:19].EntireRow.Hidden = Not CheckBox4.Value

            End With

        Next i

    End Sub

    Private Sub CheckBox5_Click()

        Dim arrShts() As Variant

        Dim i As Long

        arrShts() = Array(Sheet3.Name, Sheet4.Name, Sheet5.Name, Sheet6.Name, Sheet7.Name, Sheet8.Name, Sheet9.Name, Sheet10.Name)

        For i = LBound(arrShts) To UBound(arrShts)

            With Worksheets(arrShts(i))

                .[20:24].EntireRow.Hidden = Not CheckBox5.Value

            End With

        Next i

    End Sub

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-10-10T03:37:59+00:00

    Thank you so much!

    I can't believe I missed that and it totally makes sense. 

    I was blindly copy/pasting the formula from checkbox 1 into the rest of the the Subs.

    Was this answer helpful?

    0 comments