VBA - Loop through Controls on a form and read the value

Question

I would like to loop through controls on a form and read the value. However the Value and Checked properties are not available. My question is, how can I read the value of a control (in this case a checkbox) when I loop through them?

Dim Ctrl as Control

For Each Ctrl In frmMaintenance.Controls

    If Left(Ctrl.Name, 7) = "chkType" And **Ctrl.Value = True** Then            

    End if

Next Ctrl

Answer 1

loop through the controls and check the TypeName.

Dim c As Control


For Each c In Me.Controls
    If TypeName(c) = "CheckBox" Then
        MsgBox c.Value
    End If
Next

Answer 2

TypeName will work, but at the end of the day it's a string comparison.

The actual syntax for strongly-typed type checks in VBA goes like this:

TypeOf [object] Is [Type]

So:

Dim ctrl As Control
For Each ctrl In Me.Controls
    If TypeOf ctrl Is MSForms.CheckBox Then
        Debug.Print TypeName(ctrl), ctrl.Name, ctrl.Value
    End If
Next

Now, somewhat awkwardly the MSForms library is making OptionButton and CheckBox implement the same interface (not all that surprising actually), so the above condition will be True for both types of controls; you can filter out the OptionButton with another type check:

If TypeOf ctrl Is MSForms.CheckBox And Not TypeOf ctrl Is MSForms.OptionButton Then

Arguably, using TypeName is simpler, at least in this case where MSForms is being annoying. But you have to know about TypeOf ... Is when you start needing to do type checks in VBA.