How to cast a Checkbox Shape object to a MSForms.CheckBox object?

[SteveInAlabama]

I have a worksheet with several dozen embedded checkboxes. They are Form Controls, not ActiveX controls. I want to generate a Click event every time one of the checkboxes is clicked. I can find each checkbox in the Activesheet.Shapes collection as a Shape object, but I cannot convert the Shape object to a MSForms.CheckBox object. No matter what I've tried, I still get a Type mismatch error on the line "Set chkBoxEvent.mCheckbox = shp.OLEFormat.Object".

Here is my CCheckBoxEvent class:

    Option Explicit

    Public WithEvents mCheckbox As MSForms.CheckBox
    
    Private Sub mCheckbox_Click()
        MsgBox "Event generated"
    End Sub

Then in a standard Module, I have the following method:

    Option Explicit
    Dim chkBoxCollection As Collection
    
    Sub Set_All_ChkBoxes()
        Dim chkBoxEvent As CCheckBoxEvent
        Dim shp As Shape
        
        Set chkBoxCollection = New Collection

        For Each shp In ActiveSheet.Shapes
            If shp.Type = msoFormControl Then
                If shp.FormControlType = xlCheckBox Then
                    Set chkBoxEvent.mCheckbox = shp.OLEFormat.Object
                    chkBoxCollection.Add chkBoxEvent
                End If
            End If
        Next shp

    End Sub

Thanks in advance!

 

[tlowry]

I'm confused by your description. If you just want to capture an event when the CheckBox is clicked, I suggest the you use the "onAction" property of the CheckBox ie.

Sub setCheckBoxes()
    Dim cb
    For Each cb In ActiveSheet.CheckBoxes
        cb.OnAction = "CB_Clicked"
    Next
End Sub

Sub CB_Clicked()
    MsgBox Application.Caller
End Sub

Put the above in it's own module
Run setsetCheckBoxes() once
Test by clicking Checkboxes

Post results or clarification of what you want to do.

 

[Jon Peltier]

I'm with @tlowry on this. ActiveX controls are sexier than Forms controls, but they also can spoil your whole day. They are flaky and unstable, and they are not compatible with Excel on the Mac or on the Web. Maybe that doesn't matter to you now, but soon it will.

 

[SteveInAlabama]

Thanks to both of you for the responses. Jon, I used your awesome website (Chart Events in Microsoft Excel - Peltier Tech Blog) to add Event handlers my embedded charts, and then was trying to extend that idea to my embedded Forms checkboxes as well. Yes, I agree, the ActiveX controls are problematic, that is why I had elected to use Form controls instead. But my take-away from this is that Form controls do not allow me to extend them using WithEvents? I read @tlowery's response late, and just now got it implemented - it appears to work perfectly well, so I am satisfied with the results although I don't understand why the Forms controls won't work using WithEvents. Does it only work with OLE objects? Which I think I understand that ActiveX objects are OLE objects, but not Form controls? Any clarification you can add to clear the fog? Thanks again!

 

[Jon Peltier]

Steve -

Sometimes it's easiest just to say, That's the way Excel is. Shapes do not respond to events, and being fancy shapes, the Forms controls also do not respond to events, but you can assign a macro to run when a shape or Forms control is clicked. A chart is embedded in a chart, so you can assign a macro to a chart when it is clicked. But the chart inside the shape (inside the ChartObject) must be OLE, since it has links to the worksheet, and because it can be programmed to respond to events. The ActiveX controls by definition are OLE, and they have their own events. But you don't need to get caught up in the whole OLE infrastructure to use all the various components effectively.

 

[SteveInAlabama]

Thank you Jon, that was a great explanation! And now I 'get' what @tlowery's code does - it simply automatically assigns the same macro to each Form Control checkbox, rather than me doing it manually when designing the worksheet. Very cool! Which opens up all kinds of other possibilities, like creating checkboxes dynamically on a Worksheet_Open event based on a variable length list... but that's for another day. I love those light-bulb moments when it suddenly makes sense.