CODEWITHSUNDEEP
excelclassvba
John Nguyen
John Nguyen

Reputation: 13

Creating events for checkbox at runtime Excel VBA

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.

Upvotes: 1

Views: 2809

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33175

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

Upvotes: 1

Related Questions