loop solution for multiple controls?

[Robert]

I have a userform with 60 optionbuttons/checkboxes which uses the
following simple code to input '1' on to a 60 column list when a control
or combination of controls is selected:

If OptionButton1.Value = True Then
Selection.AutoFilter Field:=1, Criteria1:="1"
End If
If OptionButton2.Value = True Then
Selection.AutoFilter Field:=2, Criteria1:="1"
End If
'etc x 60

I imagine that some form of nested loop, one for the control and one for
the autofilter field would obviate the need for a separate entry for
each control?

Grateful for any help.

 

[Tom Ogilvy]

Dim i as Long
for i = 1 to 60
If Controls("OptionButton" & i).Value = True Then
Selection.AutoFilter Field:=i, Criteria1:="1"
End If
Next

 

[Robert]

Many thanks

If the AutoFilter field numbers started at 24 should there be a
For j - 23 To 60 and corresponding Next?

Robert

 

[Tom Ogilvy]

Autofilter field numbers start with 1 - this doesn't correspond to the
column number. So if you mean your filter starts in column 24, then no
change would be required. If you mean you want to start filtering in the
24th column in the filter range, then you would just do

Dim i as Long
for i = 1 to 60
If Controls("OptionButton" & i).Value = True Then
Selection.AutoFilter Field:=i+23, Criteria1:="1"
End If
Next

Should do what you appear to want to do.

 

[Robert]

Thanks Tom - indeed it does

Robert

 

[Tushar Mehta]

You can adapt the following:

Dim i As Byte
For i = 1 To 2
If Me.Controls("Checkbox" & i).Value Then
MsgBox "Checkbox" & i & " is true"
End If
Next i

--
Regards,

Tushar Mehta
MS MVP Excel 2000-2004
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office