Creating events for checkbox at runtime Excel VBA

Question

I am modifying the answer in this thread to make it work for my code but it is not working for me.

How to assign an event to multiple objects with excel vba?

A couple of differences, I am using a checkbox instead of a combobox but more importantly, I am creating my checkbox at runtime.

Here is my class:

Public WithEvents checkBox1 As MSForms.checkBox


Private Sub checkBox1_Click()
MsgBox "click"
End Sub'

module code:

Dim tbCollection As New Collection

Sub macro1()

Dim cbox As OLEObject
Dim myCheckBox As New JohnClass

Set cbox = ActiveSheet.OLEObjects.Add("Forms.CheckBox.1", Left:=Range("A1"))
Set myCheckBox.checkBox1 = cbox.Object
tbCollection.Add cbox
end sub

I can see that I have a reference to the newly created check box because I can change the caption but when I click on it, nothing happens.

Answer 1

You need to add the instance of the custom class to the collection. Change

tbCollection.Add cbox

to

tbCollection.Add myCheckBox

Update:

There seems to be some problem with adding OLEObjects at runtime and running any other code. I don't know what the problem is, but this seems to work.

Public tbCollection As Collection

Sub macro1()

    ActiveSheet.OLEObjects.Add ClassType:="Forms.CheckBox.1", Left:=1, Top:=1

    Application.OnTime Now, "AddToClass"

End Sub

Sub AddToClass()

    Dim myCheckBox As JohnClass

    Set tbCollection = New Collection
    Set myCheckBox = New JohnClass

    Set myCheckBox.CheckBox1 = ActiveSheet.OLEObjects(ActiveSheet.OLEObjects.Count).Object
    tbCollection.Add myCheckBox

End Sub