PDA

View Full Version : Problem Hiding Rows Based on CheckBox Event - Reaching Maximum Limit



sxt173
04-19-2012, 09:38 AM
Hi all,

I have been a long time follower of the forums, 1st time posting. Pretty noob at VBA. I have a sheet with a hierarchy of operating units in a column, with DirectX checkboxes next to each (used a VBA script to auto-create the checkboxes ).

The checkboxes go from D5 to D147, so 142 checkboxes. They are named checkbox_D5, checkbox_D6 all the way to 147, so checkbox_[Column]&[Row].

Based on a True/False value in another column to the right (BA or column#53), I am hiding specific rows. Just to give a sense, if a checkbox for a Level 2 hierarchy is clicked, I have some formulas to determine which Level 3 rows will be shown by putting a TRUE in column BA. For sake a speed, I created these to only run for 30 rows per click (that's the most that would ever change on a checkbox click).

I did not know how to do it any cleaner, so I created 142 of these:


Private Sub CheckBox_D5_Change()
Dim i As Long
For i = 5 To i + 30
If Cells(i, 53) = False Then
Rows(i).Hidden = True
Else: Rows(i).Hidden = False
End If
Next i
End Sub

' second one would be checkbox on the next row
Private Sub CheckBox_D6_Change()
Dim i As Long
For i = 6 To i + 30
If Cells(i, 53) = False Then
Rows(i).Hidden = True
Else: Rows(i).Hidden = False
End If
Next i
End Sub
' continued like that until D147


It works OK up to checkbox_D23 or 24, but excel seems to reduce the number of rows it goes through for each checkbox below that, with checkbox_D31 down doing nothing. I read somewhere about a 32 form item limit, wonder if I'm hitting that.. Ideas? Please? Cleaner way to do this vs. 142 instances of checkbox_XX_change() events?

Bob Phillips
04-19-2012, 04:10 PM
You could create form vents to process all the checkboxes, or just try this technique

Private Sub CheckBox_D5_Change()
Call HideEm(5)
End Sub

' second one would be checkbox on the next row
Private Sub CheckBox_D6_Change()
Call HideEm(6)
End Sub

Private Function HideEm(startat as long)
Dim i As Long
For i = startat To startat + 30
Rows(i).Hidden = Cells(i, 53).Value = False
Next i
End Function