在VBA中以编程方式创建事件侦听器 [英] Programmatically create event listener in VBA

问题描述

可以在 comboBox 上以编程方式创建事件方法？

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

在工作表上，我有一个 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

如何创建和分配事件方法 for ole.Name ：

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

 Private Sub myComboBox_Change()
   ...
 End Sub

在Java中，可以通过以下方式完成： myComboBox.setOnChangeListener（...一些监听器接口的代码）;）

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

推荐答案

您需要创建一个具有声明为WithEvents的组合框变量的类模块。然后当您创建组合框时，将其分配给类的变量。这样，您可以在设计时编写事件过程，但只有在组合框创建后才能收听。

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.

创建一个名为CControlEvents的类模块

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.

您必须以与创建它相同的过程来连接组合框。有一个错误（或功能） ），阻止在相同的过程中进行。与设计模式有关，我想。这就是为什么在创建代码完成后使用Application.OnTime来运行连接代码。

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.

这篇关于在VBA中以编程方式创建事件侦听器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！