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.

Daniel Dušek · Accepted Answer · 2016-09-08T16:51:07.413

2

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

edited Sep 08 '16 at 16:51

answered Sep 08 '16 at 13:21

Daniel Dušek

13,683
5
36
51

Shai Rado · Answer 2 · 2016-09-08T13:01:12.487

I couldn't find (so far) how to assign it to a CheckBox, I found how to assign it to a OLEObject variable (if that helps you).

Sub HandleCheckBoxClick(nm As String)

Dim o           As OLEObject
Dim CB          As CheckBox
Dim oleObj      As OLEObject

' loop though all OLEObjects in "Sheet2" >> modify to your sheet name
For Each o In Sheets("Sheet2").OLEObjects
    If TypeName(o.Object) = "CheckBox" Then
        Debug.Print o.Name

        ' if you have more then 1 CheckBox
        If o.Name = nm Then
            Set oleObj = o
'            Set CB = o.Select
        End If
    End If
Next o

' just to test the options with OLEObject variable
With t
    t.Top = 100
    If t.Object.Value = True Then
        MsgBox "CheckBoX " & nm & " is selected"
    Else
        MsgBox "CheckBoX " & nm & " is not selected"
    End If
End With

End Sub

score 0 · Answer 3 · answered Dec 25 '20 at 16:42

0

This worked for me to get the checkbox state

Dim checkBox1 As Object
Set checkBox1 = Sheet1.OLEObjects("CheckBox1").Object
MsgBox checkBox1.Value

answered Dec 25 '20 at 16:42

LeslieM

2,105
1
17
8

VBA: Assign a generic OLEObject to a CheckBox variable

3 Answers3