Thread: Multiple Checkbox Formula

Hello,

I'm new to VBA and i'm looking to clean up my coding for linking mulitple checkboxes to a command button. I have a spreadsheet called "Layout" which contains the layout of the yard of the company I work for. I made a diagram of the units stored in the yard by adding borders to the cells (6X1) * 75 spread throughout the spreadsheet. Now on the second spreadsheet called "Reports" I have defficiencies reports (A1:J35) through to (A2591:J2625). So there are 75 reports. My goal was to hide all rows containing reports (A1:J2625) and then when the checkboxes beside each unit are checked then the corresponding report rows are unhidden. The coding I have so far is:

Sub Button80_Click()

If Sheets("Layout").CheckBox1.Value = True Then
Sheets("Reports").Select
[1:35].EntireRow.Hidden = False
Else:
Sheets("Reports").Select
[1:35].EntireRow.Hidden = True
End If

' Repeated 74 times with 35 row increments ie. the next would be [36:70]
' This code works but it took a long time to manually input
End Sub

What I would like to have is a formula that only needs to be inputed once and covers all 75 checkboxes. What I have so far (doesn't work) is:

Sub Button81_Click()

Dim x as integer
For x = 1 To 75

If (Sheets("Layout").Control("CheckBox" & x).Value) Then
Sheets("Reports").Select
[(((x-1)*35) +1):(x*35)].EntireRow.Hidden = True  

Else:
[(((x-1)*35) +1):(x*35)].EntireRow.Hidden = False  

End If

Next x

End Sub

Any help is much appreciated

Cory