excel - Programmatically create event listener in VBA

Question

Is it possible to programmatically create an event method on a comboBox?

On worksheet I have a ComboBox and I can get its names by code:

       Dim ole As OLEObject
       For Each ole In ActiveSheet.OLEObjects

       If TypeName(ole.Object) = "ComboBox" Then
       ' ole.Name '<<<<<<<< here 
       End If
       Next ole

How can I now create and assign an event method for ole.Name:

 Private Sub myComboBox_Change()
   ...
 End Sub

In Java it can be done with: myComboBox.setOnChangeListener(...some code of listener interface...) ;)

Answer 1

You need to create a class module with a combobox variable declared WithEvents. Then when you create the combobox, assign it to the class' variable. This way, you can write your event procedure at design time, but have it listen only after the combobox is created.

Create a class module called CControlEvents

Private WithEvents mclsCbx As MSForms.ComboBox

Public Property Set Cbx(ByVal clsCbx As MSForms.ComboBox): Set mclsCbx = clsCbx: End Property
Public Property Get Cbx() As MSForms.ComboBox: Set Cbx = mclsCbx: End Property

Private Sub mclsCbx_Change()

    MsgBox Me.Cbx.name

End Sub

Then in a standard module

'this is public so it doesn't go out of scope
Public gclsControlEvents As CControlEvents

Sub MakeCombo()

    Dim oleCbx As OLEObject

    'Create the combobox
    Set oleCbx = Sheet1.OLEObjects.Add("Forms.ComboBox.1")
    oleCbx.Object.AddItem "1"
    oleCbx.Object.AddItem "2"

    'hookup the events
    Application.OnTime Now, "HookupEvents"

End Sub

Sub HookupEvents()

    Set gclsControlEvents = New CControlEvents
    Set gclsControlEvents.Cbx = Sheet1.OLEObjects(1).Object

End Sub

Now when the combobox changes, the event will fire.

You have to hookup the combobox in a different procedure than the one you create it in. There is a bug (or feature) that prevents doing it in the same procedure. Something to do with Design Mode, I think. That's why you use Application.OnTime to run the hookup code after the creation code completes.