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?
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?