VBA - Sheet Checkboxes - Intrinsic and ActiveX

A SO question came up about accessing an ActiveX checkbox. Here is some code to programmatically access check-boxes. Intrinsic check-boxes are found via the Shapes collection whilst ActiveX check-boxes are found via the OLEObjects collection. Also, adding an ActiveX control adds a module level variable to the sheet module for best access. However, SO OP was having difficulty relying upon this.

Option Explicit



Friend Function IntrinsicCheckBox() As CheckBox
    '* use this if you inserted an intrinsic Excel Checkbox, default name Check Box 1
    Dim myMSFormsCheckbox As Object
    Set myMSFormsCheckbox = Me.Shapes.Item("Check Box 1")
    
    Dim chk As Object
    Set chk = myMSFormsCheckbox.OLEFormat.Object

    Dim chk2 As CheckBox
    Set IntrinsicCheckBox = chk

End Function


Friend Function ActiveXCheckBox() As MSForms.CheckBox
    '* use this if you inserted a ActiveX Checkbox
    Dim myActiveXCheckbox As OLEObject
    Set myActiveXCheckbox = Me.OLEObjects.Item("CheckBox1")
    
    Dim chk As MSForms.CheckBox
    Set chk = myActiveXCheckbox.Object

    Set ActiveXCheckBox = chk

End Function

Object Linking and Embedding still going strong

OLE stands for Object Linking and Embedding and COM was invented to facilitate OLE. Nice to see all the COM interfaces still working.

Inserting a worksheet into Word document

Open Word, create new document go to VBA IDE and paste in the following code to see how to insert a worksheet and script against the worksheet as if you were in Excel VBA.

Option Explicit
'Paste into Word VBA
Sub ObjectLinkingAndEmbeddingExample()

    Dim shp As Shape
    Set shp = ThisDocument.Shapes.AddOLEObject("Excel.Sheet")
    
    shp.OLEFormat.Object.Sheets(1).Cells(1, 1) = "OLE works!"


End Sub

It would appear the gateway through to the Excel object model is given by OLEFormat.Object on the shape class.

Simulating an ActiveX command button click in code

Sometimes the gateway can require two successive calls to Object. In an Excel worksheet go to Developer mode and insert an *ActiveX* command button (not intrinsic Excel button), then double click to get to Click handler code and add the following

Option Explicit

Private Sub CommandButton1_Click()
    MsgBox "CommandButton1_Click"
End Sub

Then come out of developer mode and click the button to ensure the message box is thrown. Then insert the following code in a non-class module and run it.

Option Explicit

Sub SimulateButtonClick()

    '* After adding an *ActiveX* command button on the activesheet

    Dim shp As Shape
    Set shp = ActiveSheet.Shapes(1)
    
    Debug.Assert TypeName(shp.OLEFormat.Object.Object) = "CommandButton"
    
    Dim cmd As CommandButton
    Set cmd = shp.OLEFormat.Object.Object
    
    '* The next line compiles but Value is not is the Intellisense dropdown menu.
    '* It mimics the clicking on the button and should throw the message box in
    '* above handler code (see block above)
    cmd.Value = True

End Sub

Running this code will mimic the button click. You can see how in this case you need two calls to Object to get the plumbing right otherwise you get a type mismatch error