Excel VBA how to link a class and a control?

Question

I am using Excel 2003 with VBA, I am dynamically creating check box controls on a sheet and want to link the VBA controls to a class so that when a user clicks on a checkbox an event is fired so I can do something.

From what I've read it would seem that creating a user class is the solution, but having tried this I can't get it to work.

My user class looks like this:

    Option Explicit

    Public WithEvents cbBox As MSForms.checkbox

    Private Sub cbBox_Change()
        MsgBox "_CHANGE"
    End Sub

    Private Sub cbBox_Click()
        MsgBox "_CLICK"
    End Sub

My code to create the checkboxes:

    For Each varExisting In objColumns
    'Insert the field name
        objColumnHeadings.Cells(lngRow, 1).Value = varExisting
    'Insert a checkbox to allow selection of the column
        Set objCell = objColumnHeadings.Cells(lngRow, 2)
        Dim objCBclass As clsCheckbox
        Set objCBclass = New clsCheckbox
        Set objCBclass.cbBox = ActiveSheet.OLEObjects.Add( _
                                  ClassType:="Forms.CheckBox.1" _
                                , Left:=300 _
                                , Top:=(objCell.Top + 2) _
                                , Height:=10 _
                                , Width:=9.6).Object
        objCBclass.cbBox.Name = "chkbx" & lngRow
        objCBclass.cbBox.Caption = ""
        objCBclass.cbBox.BackColor = &H808080
        objCBclass.cbBox.BackStyle = 0
        objCBclass.cbBox.ForeColor = &H808080
        objCheckboxes.Add objCBclass
        lngRow = lngRow + 1
    Next

The checkboxes are visible in the sheet, but when I click on them, no message box is displayed so the link to the class doesn't seem to be working.

Why?

Edit...If after adding the checkboxes I go into the VB IDE and select one of the created checkboxes from the list of controls, then select Click from the Procedure drop down list, it will insert the code for a call back which if I add a message box to this, works when I click on the same checkbox...so how can I achieve this in code? I've tried recording a macro to do this, nothing was recorded.

I realize that, but I've occasionally made the mistake of declaring it within the sub which populates it, instead of as a global.... — Tim Williams, Jul 19 '16 at 14:51

score 2 · Accepted Answer · edited Jul 18 '16 at 20:01

Edit by S.Platten, jump to the bottom for how this helped me fix the problem...

Due to some weird reason, VBA doesn't hook up the events for Sheet's ActiveX control in the same execution cycle in which they were added. So, we need to come out of the cycle which added the controls and then invoke the event adding proc in next cycle. Application.OnTime helps here.

Its seems a bit of overkill but it works :)

Option Explicit

 Dim collChk         As Collection
 Dim timerTime

 Sub master()

        '/ Add the CheckBoxes First
        Call addControls

        '<< Due to some weird reason, VBA doesn't hook up the events for Sheet's ActiveX control in the same
        'execution cycle in which they were added. So, we need to come out of the cycle which added the controls
        'and then invoke the event adding proc in next cycle. >>

        '/ Start Timer. Timer will call the sub to add the events
        Call StartTimer
 End Sub

Sub addControls()
    Dim ctrlChkBox      As MSForms.CheckBox
    Dim objCell         As Range
    Dim i               As Long

    'Intialize the collection to hold the classes
    Set collChk = New Collection

    '/ Here Controls are added. No Events, yet.
    For i = 1 To 10
        Set objCell = Sheet1.Cells(i, 1)
        Set ctrlChkBox = Sheet1.OLEObjects.Add( _
                          ClassType:="Forms.CheckBox.1" _
                        , Left:=1 _
                        , Top:=(objCell.Top + 2) _
                        , Height:=objCell.Height _
                        , Width:=100).Object
        ctrlChkBox.Name = "chkbx" & objCell.Row
     Next

End Sub

Sub addEvents()

    Dim ctrlChkBox      As MSForms.CheckBox
    Dim objCBclass      As clsCheckBox
    Dim x               As Object


    'Intialize the collection to hold the classes
    Set collChk = New Collection

    '/ Here we assign the event handler
     For Each x In Sheet1.OLEObjects
       If x.OLEType = 2 Then

        Set ctrlChkBox = x.Object

        Set objCBclass = New clsCheckBox
        Set objCBclass.cbBox = ctrlChkBox

        collChk.Add objCBclass
        Debug.Print x.Name
       End If
    Next

    '/ Kill the timer
    Call StopTimer

End Sub

Sub StartTimer()
    timerTime = Now + TimeSerial(0, 0, 1)
    Application.OnTime EarliestTime:=timerTime, Procedure:="addEvents", _
        Schedule:=True
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=timerTime, Procedure:="addEvents", _
        Schedule:=False
End Sub

Class Module: clsCheckBox

    Option Explicit

    Public WithEvents cbBox As MSForms.CheckBox

    Private Sub cbBox_Change()
        MsgBox "_CHANGE"
    End Sub

    Private Sub cbBox_Click()
        MsgBox "_CLICK"
    End Sub

Edit continued...

The class (clsCheckbox):

    Option Explicit

    Public WithEvents cbBox As MSForms.checkbox

    Private Sub cbBox_Click()
        MsgBox "_CLICK"
    End Sub

Module1

    Public objCheckboxes As Collection
    Public tmrTimer

    Public Sub addEvents()
        Dim objCheckbox As clsCheckbox
        Dim objMSCheckbox As Object
        Dim objControl As Object

        Set objCheckboxes = New Collection
        For Each objControl In Sheet1.OLEObjects
            If objControl.OLEType = 2 _
            And objControl.progID = "Forms.CheckBox.1" Then
                Set objMSCheckbox = objControl.Object
                Set objCheckbox = New clsCheckbox
                Set objCheckbox.cbBox = objMSCheckbox
                objCheckboxes.Add objCheckbox
            End If
        Next
        Call stopTimer
    End Sub

    Public Sub startTimer()
        tmrTimer = Now + TimeSerial(0, 0, 1)
        Application.OnTime EarliestTime:=tmrTimer _
                         , Procedure:="addEvents" _
                         , Schedule:=True
    End Sub

    Public Sub stopTimer()
        On Error Resume Next
        Application.OnTime EarliestTime:=tmrTimer _
                         , Procedure:="addEvents" _
                         , Schedule:=False
    End Sub

The code in the sheet that adds the controls:

    Dim objControl As MSForms.checkbox
    For Each varExisting In objColumns
    'Insert the field name
        objColumnHeadings.Cells(lngRow, 1).Value = varExisting
    'Insert a checkbox to allow selection of the column
        Set objCell = objColumnHeadings.Cells(lngRow, 2)
        Set objControl = ActiveSheet.OLEObjects.Add( _
                                  ClassType:="Forms.CheckBox.1" _
                                , Left:=300 _
                                , Top:=(objCell.Top + 2) _
                                , Height:=10 _
                                , Width:=9.6).Object
        objControl.Name = "chkbx" & lngRow
        objControl.Caption = ""
        objControl.BackColor = &H808080
        objControl.BackStyle = 0
        objControl.ForeColor = &H808080
        lngRow = lngRow + 1
    Next

This isn't the entire project, but enough to demonstrate the workings.

I'm getting a type mismatch on the line this line: Set ctrlChkBox = x.Object I've checked the code and I'm sure I've copied it correctly. — SPlatten, Jul 18 '16 at 18:33
Hmm,,,,, not sure why its happening to you. Just tested the code and it worked. you are using same declaration `Dim ctrlChkBox As MSForms.CheckBox` in addControls and addEvents? — cyboashu, Jul 18 '16 at 18:38
Just copy this whole code set in a new work book and see if it works. Hopefully you missed something while copy paste. :) — cyboashu, Jul 18 '16 at 18:42
I did this just now, it errors immediately on: Dim ctrlChkBox As MSForms.Checkbox with "User-defined type not defined" — SPlatten, Jul 18 '16 at 18:48
ah, I see you need to add a reference to `Microsoft Forms 2.0 Object Library` in VBA IDE Tools - References. Or you can just add a blank `UserForm` to your project and the reference will appear. — cyboashu, Jul 18 '16 at 18:49
I already have this reference in my workbook and I still get Method or data member not found on MSForms.checkbox — SPlatten, Jul 18 '16 at 18:53
Hmm.. then I have no more ideas. all out :) Can't reproduce the issue at my end. — cyboashu, Jul 18 '16 at 18:55
In my workbook I have a single Sheet (Sheet1), a single form, two modules and the class, references include: Visual Basic For Applications, Microsoft Excel 11.0 Object Library, OLE Automation, Microsoft Office 11.0 Object Library, Microsoft Forms 2.0 Object Library, Microsoft Windows Common Controls-2 6.0 (SP6), Microsoft Calendar Control 11.0, Microsoft ActiveX Data Objects 2.8 Library — SPlatten, Jul 18 '16 at 18:57
What version of Excel do you have? I have 2003 (11.8404.8405) SP3 — SPlatten, Jul 18 '16 at 19:00
Is it possible to embed a form in a sheet? If yes, I could use standard form controls. — SPlatten, Jul 18 '16 at 19:05
Ok, I got it working, I will edit your post to show what I had to do. — SPlatten, Jul 18 '16 at 19:53

score 0 · Answer 2 · edited May 23 '17 at 11:51

You are currently using ActiveX controls. Yet, ActiveX controls are bound to specific naming conventions. For example: if you insert an ActiveX button onto a sheet and name it btnMyButton then the sub must be named btnMyButton_Click. The same applies to checkboxes. If you insert a new checkbox with the name CheckBox2 then the sub's name must be CheckBox2_Click. In short, there cannot be a sub with the name cbBox_Change associated to any ActiveX checkbox.

So, what you really need (with ActiveX controls) is a way to change the VBA code on a sheet. But thus far I have never come across any such code (VBA code to change VBA code on a sheet).

A much easier route would be if you'd be willing to use form controls instead.

The following sub will create a (form control) checkbox and assign the macro tmpSO to it. The sub tmpSO (unlike subs for ActiveX controls) does not need to reside on the sheet but can be in any module.

Sub Insert_CheckBox()

Dim chk As CheckBox

Set chk = ActiveSheet.CheckBoxes.Add(390.75, 216, 72, 72)
chk.OnAction = "tmpSO"

End Sub

Since a from control is calling the sub tmpSO you can use Application.Caller in that sub and thereby know which checkbox has been calling this sub.

Sub tmpSO()

Debug.Print Application.Caller

End Sub

This will return the name of the CheckBox. So, you can use this one sub for all of your checkboxes any dynamically handle them based on their names (possibly using a Case Select).

Here is another example for tmpSO:

Sub tmpSO()

With ThisWorkbook.Worksheets(1).CheckBoxes(Application.Caller)
    MsgBox "The checkbox " & Application.Caller & Chr(10) & _
        "is currently " & IIf(.Value = 1, "", "not") & " checked."
End With

End Sub

Thank you, there are posts doing something close with buttons created in the same way, but I cannot see how these work with the user class and having tried it myself I cannot get it to fire the events. — SPlatten, Jul 17 '16 at 19:55
You can use the VBA Extensibility Library to use VBA code to write sheet code. You do have to **Trust** access to the VBA Project in your security settings though. A specific example is given at [Excel vba add code to sheet module programmatically](http://stackoverflow.com/questions/34837006/excel-vba-add-code-to-sheet-module-programmatically) and a more general discussion at Chip Pearson's [Programming The VBA Editor](http://www.cpearson.com/Excel/VBE.aspx) — Ron Rosenfeld, Jul 17 '16 at 20:15
@SPlatten I am not sure what your problem / question is. Just copy over the sub `Insert_CheckBox` and the sub `tmpSO`. Then run `Insert_CheckBox`. Afterwards, you'll have a checkbox on which ever sheet is active and if you click on that checkbox then you'll get a `debug.print` or a `MsgBox` (depending on whichever tmpSO sub you copied over). That's it. Done. No class module, no additional events to be coded. It is as simple as that. — Ralph, Jul 17 '16 at 20:53
The problem is that everything is created in code, nothing is placed on the sheet at runtime, as a consequence I need to be able to create the event handlers at runtime. And so far nothing works. — SPlatten, Jul 18 '16 at 19:48

Excel VBA how to link a class and a control?

2 Answers2

Linked