Form Checkbox

[Owen]

I am wondering if it is possible to use a For...Next method on a UserForm
which contains 13 checkboxes, where i need to evaluate and If/Then/Else
statement.

For example, currently my If/Then/Else works by evaluating each of the 13
checkboxes separately which makes for a lot of code.ie

If UserForm1.CheckBox1.value = True Then
....
....
Else
If UserForm1.CheckBox2.value = True Then etc

Can i use a For Next method along the lines of
For j= 1 to 13
If UserForm1.Checkbox(j).value = True Then
....
End If
Next J

Not sure if this is possible but would appreciate any help so i can reduce
the amount of code i need to reproduce.

Thanks

 

[Jay Freedman]

If all the checkboxes are named with the same beginning part plus a number (like
the default names CheckBox1, CheckBox2, etc.), you can form a string containing
the name and use that as the "index" into the UserForm's Controls collection:

Dim j As Integer
For j = 1 To 3
If Me.Controls("CheckBox" & j).Value Then
MsgBox "CheckBox" & j & " is checked"
End If
Next

If you would rather name the checkboxes according to their meanings (which is
usually advisable), then you can loop through the whole Controls collection and
pick out just the checkboxes.

Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = "CheckBox" Then
If ctl.Value Then
MsgBox ctl.Name & " is checked"
End If
End If
Next

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all
may benefit.

 

[Owen]

Thanks Jay, that is awesome.

If all the checkboxes are named with the same beginning part plus a number (like
the default names CheckBox1, CheckBox2, etc.), you can form a string containing
the name and use that as the "index" into the UserForm's Controls collection:

Dim j As Integer
For j = 1 To 3
If Me.Controls("CheckBox" & j).Value Then
MsgBox "CheckBox" & j & " is checked"
End If
Next

If you would rather name the checkboxes according to their meanings (which is
usually advisable), then you can loop through the whole Controls collection and
pick out just the checkboxes.

Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = "CheckBox" Then
If ctl.Value Then
MsgBox ctl.Name & " is checked"
End If
End If
Next

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all
may benefit.