Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
970 views
in Technique[技术] by (71.8m points)

excel - Programmatically create event listener in VBA

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 Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...