在 VBA 中以编程方式创建事件侦听器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21738405/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Programmatically create event listener in VBA
提问by Ivan Podhornyi
Is it possible to programmatically create an event methodon a comboBox?
是否可以以编程方式event method在 a 上创建一个comboBox？
On worksheet I have a ComboBoxand I can get its names by code:
在工作表上，我有一个ComboBox，我可以通过代码获取它的名称：
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 methodfor ole.Name:
我现在如何创建和分配event methodfor ole.Name：
Private Sub myComboBox_Change()
...
End Sub
In Java it can be done with: myComboBox.setOnChangeListener(...some code of listener interface...);)
在 Java 中，它可以通过以下方式完成：myComboBox.setOnChangeListener(...some code of listener interface...);)
回答by Dick Kusleika
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.
您需要创建一个带有声明为 WithEvents 的组合框变量的类模块。然后，当您创建组合框时，将其分配给类的变量。这样，您可以在设计时编写事件过程，但仅在创建组合框后让它侦听。
Create a class module called CControlEvents
创建一个名为 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.
您必须在与创建组合框不同的程序中连接组合框。有一个错误（或功能）阻止在同一程序中执行此操作。我认为与设计模式有关。这就是为什么在创建代码完成后使用 Application.OnTime 运行连接代码的原因。
