VBA: Assign a generic OLEObject to a CheckBox variable

Question

My code loops through all of the existing OLEObjects within the current sheet in an Excel workbook. I want it to find a specific one (based on the name passed to the sub) which is always a CheckBox and assign it to a variable of type CheckBox.

Note: The other objects are not all Checkboxes, hence the generic OLEObject type.

Example code that calls sub, showing example of name:

HandleCheckBoxClick("chkAddSummary")

Sub that looks for this specific object:

Sub HandleCheckBoxClick(nm As String)

    Dim o As OLEObject
    Dim cb As CheckBox

    For Each o In Me.OLEObjects
        If o.name = nm Then
            Set cb = o
        End If
    Next o

End Sub

I found a very similar question at: Excel VBA: how to cast a generic control object into a ComboBox object? but it refers to Form Controls (not ActiveX Controls). I tried the method given in the answer to see whether it was transferrable between the two control types but had no success.

The reason I want to do this is similar to the asker of the question I refer to - I cannot use methods like CheckBox.Value with a generic OLEObject variable.

I have also tried using the OLEObject.progID method to make sure o is a checkbox object. The error I get when trying to Set cb = o is a Type Mismatch.

Answer 1

When declared as MSForms.CheckBox it then should be possible to assign o.Object.

Sub test()
    HandleCheckBoxClick "chkAddSummary"
End Sub

Sub HandleCheckBoxClick(nm As String)

    Dim o As OLEObject
    Dim CB As MSForms.CheckBox

    For Each o In Me.OLEObjects
        If o.Name = nm Then
            Set CB = o.Object
        End If
    Next o

End Sub