I have struggled with this problem for while now...I want to do something very simple. I want to create multiple commandbuttons at runtime, and then handle events for these commandbuttons with one procedure. So I have built a "withevents" class to handle the automation, but my code is not working. When I run Test(), the CommandButton is created, but when I click on it...there is no messagebox response...I cannot find the error..Please any help would be great!!
Class cTest
Public WithEvents Button As MSForms.CommandButton
Public Sub Button_Click()
s = MsgBox("Hello", vbOKOnly)
End Sub
Module 1
Public TestCollection As Collection
Sub Test()
Set TestCollection = New Collection
Dim Btn As CommandButton
Dim OLEBtnObj As cTest
Set OLEBtnObj = New cTest
Set Btn = Sheet1.OLEObjects.Add(ClassType:="Forms.CommandButton.1", link:=False,_ DisplayAsIcon:=False, Left:=368.25, Top:=51, Width:=44.25, Height:=24).Object
Set OLEBtnObj.Button = Btn
TestCollection.Add Item:=OLEBtnObj
End Sub
I have one rather impractical solution :-). To test it place the following code in Sheet1 Class Module.
For each new Sheet1-button a new event handled will be added. This event handler will execute the common event handler and pass the name of the clicked command button to it.