Loop through Checkboxes in frame

[sharky12345]

Is there a way I can loop through all Checkboxes in a Frame on a Userform?

All of them are named individually, so not CheckBox1, 2 etc if that makes a difference.

 

[VBA Geek]

sub a
Dim ctrl As MSForms.Control

For Each ctrl In Me.[COLOR=#ff0000][B]Frame1[/B][/COLOR].Controls
    If TypeName(ctrl) = "CheckBox" Then
        MsgBox "This is checkbox: " & ctrl.Name
    End If
Next ctrl
end sub

 

[sharky12345]

VBA Geek,

Thanks - very helpful.....could you assist me in showing what I would add to check if any are selected?

Essentially what I now need to do is assess if there are non selected.

 

[Haluk]

Alternative:

You can use the TypeOf method as well....

Private Sub CommandButton1_Click()
    Dim Ctrl As Control
    For Each Ctrl In Me.Frame1.Controls
        If TypeOf Ctrl Is MSForms.CheckBox Then
            MsgBox "Name=" & Ctrl.Name & vbCrLf & "Selected =" & Ctrl.Value
        End If
    Next
End Sub

 

[Haluk]

And, this code will inform you the unselected CheckBoxes in Frame1 of the UserForm.

Private Sub CommandButton1_Click()
    Dim Ctrl As Control
    For Each Ctrl In Me.Frame1.Controls
        If TypeOf Ctrl Is MSForms.CheckBox Then
            If Ctrl.Value = False Then
                MyMsg = MyMsg & vbCrLf & Ctrl.Name & " is not selected"
            End If
        End If
    Next
    If MyMsg = Empty Then
        MsgBox "All of them are selected"
    Else
        MsgBox MyMsg
    End If
End Sub

 

[RoryA]

Alternative:

You can use the TypeOf method as well....

That might not work properly though. TypeOf would also include ToggleButtons and Optionbuttons since they also implement the Checkbox interface.

 

[Haluk]

TypeOf would also include ToggleButtons and Optionbuttons since they also implement the Checkbox interface.

I didn't know that, thanks for this info.

So, using VBA Geek's TypeName method, my last post should be modified as;

Private Sub CommandButton1_Click()
    Dim Ctrl As Control
    For Each Ctrl In Me.Frame1.Controls
        If TypeName(Ctrl) = "CheckBox" Then
            If Ctrl.Value = False Then
                MyMsg = MyMsg & vbCrLf & Ctrl.Name & " is not selected"
            End If
        End If
    Next
    If MyMsg = Empty Then
        MsgBox "All of them are selected"
    Else
        MsgBox MyMsg
    End If
End Sub