For each...loop for checkboxes

[Steven Cheng]

I have a userform that has several (30) checkboxes and
want to test which ones have been selected by showing the
checkbox names through a messagebox.

I started my code off like this once the commandbutton on
the userform has been selected:

private sub commandbutton1_click()

dim r as checkbox
dim w as string

w = ""

for each r in userform1
if r.value then
w = w & r.caption & vbcr
end if
next

end sub

But it appears that I am not getting the right
object/collection combination.

 

[Tom Ogilvy]

Private Sub commandbutton1_click()

Dim r As Control
Dim w As String

w = ""

For Each r In UserForm2.Controls
If TypeOf r Is msforms.CheckBox Then
If r.Value Then
w = w & r.Caption & vbCr
End If
End If
Next
msgbox w
End Sub

Tested in xl97

 

[Doug Glancy]

Tom,

Can you explain the difference between the second line of your For
loop and the following:

If TypeName(r) = "CheckBox" Then

Thanks,

Doug

 

[Tom Ogilvy]

That is just another way to check. - only danger there is you don't spell
CheckBox exactly right.

Regards,
Tom Ogilvy

 

[Steven Cheng]

Tom/Doug...thanks. But this implies that it is looking
for a forms control, right? As opposed to its ActiveX
counterpart?