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...) ;)

See "creating an event procedure" - cpearson.com/excel/vbe.aspx — Tim Williams
– Tim Williams, Commented Feb 12, 2014 at 20:31
Can you not have it set up at design time instead of run time? — Dick Kusleika
– Dick Kusleika, Commented Feb 12, 2014 at 20:37
Maybe this question may help? stackoverflow.com/questions/14994235/… — hstay
– hstay, Commented Feb 13, 2014 at 8:15
Out of curiosity, why do you have to do this programmatically? Is it because you create those objects dynamically? — sam092
– sam092, Commented Feb 13, 2014 at 10:10

Dick Kusleika · Accepted Answer · 2014-03-09 22:15:09Z

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.

Collectives™ on Stack Overflow

Programmatically create event listener in VBA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related