[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 FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all
may benefit.

On Sat, 18 Jul 2009 07:11:01 -0700, Owen wrote:

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

[owen]

Thanks Jay, that is awesome.

"Jay Freedman" wrote:

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 FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all
may benefit.

On Sat, 18 Jul 2009 07:11:01 -0700, Owen wrote:

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