vba 如何使用excel vba将事件分配给多个对象？

How to assign an event to multiple objects with excel vba?

提问by Alex P

I have ten drop down menus on a worksheet each of which should respond the same to the GotFocus()event.

我在工作表上有十个下拉菜单，每个菜单都应该对GotFocus()事件做出相同的响应。

I have written the following code but I get a run time error (459) - "Object or class does not support the set if events"

我编写了以下代码，但出现运行时错误 (459) - “对象或类不支持设置的事件”

In a class called clsPDRinputI have the following:

在一个叫做clsPDRinput我的班级中，我有以下内容：

Public WithEvents inputObj As OLEObject

Public Property Set myInput(obj As OLEObject)
    Set inputObj = obj
End Property

Public Sub tbPDRInput_GotFocus()
    //Do some stuff...
End Sub

I am then running the following code which is producing the error:

然后我运行以下产生错误的代码：

Dim tbCollection As Collection

Public Sub InitializePDRInput()
    Dim myObj As OLEObject
    Dim obj As clsPDRInput

    Set tbCollection = New Collection
        For Each myObj In Worksheets("1. PDR Documentation").OLEObjects
            If TypeName(myObj.Object) = "ComboBox" Then
                Set obj = New clsPDRInput
                Set obj.myInput = myObj <-- **THIS LINE THROWS ERROR**
                tbCollection.Add obj
            End If
        Next myObj
    Set obj = Nothing
End Sub

I am not sure what is causing this error. One though I had is that OLEObjectis too generic and not every OLEObjectsupports the GotFocus()event and that is why the code is giving the error message?

我不确定是什么导致了这个错误。虽然我有一个OLEObject是太通用了，并不是每个人都OLEObject支持该GotFocus()事件，这就是代码给出错误消息的原因？

I have tried replacing OLEObjectwith MSForms.ComboBoxbut that doesn't resolve issue.

我试过用替换OLEObject，MSForms.ComboBox但这并不能解决问题。

Any ideas - have googled for two hours now and come up blank...

任何想法 - 现在已经用谷歌搜索了两个小时，却发现一片空白......

EDIT - Update on what I think the issue is...

编辑 - 更新我认为问题是......

I did more investigating and here is what the issue is as far as I can tell.

我做了更多调查，据我所知，这是问题所在。

1. If you declare a variable as OLEObject(as in ...inputObj as OLEObject) then the only eventsexposed are GotFocus()and LostFocus().
2. If you declare a variable as MSForms.ComboBox(as in ...inputObj as MSForms.ComboBox) then a variety of events are exposed (e.g. Change(), Click(), DblClick()) butthe events GotFocus()and LostFocus()are notexposed

1. 如果您将变量声明为OLEObject（如 in ...inputObj as OLEObject），则唯一公开的事件是GotFocus()和LostFocus()。
2. 如果您声明一个变量MSForms.ComboBox（如...inputObj as MSForms.ComboBox），那么各种各样的事件暴露（例如Change()，Click()，DblClick()），但该事件GotFocus()，并LostFocus()在未暴露

Points 1 and 2 are consistent with the object model in excel. As a result, when I try to assign a ComboBoxto my class I get an error (see original post) as the ComboBoxdoes not support the GotFocus()and LostFocusevents.

第1点和第2点与excel中的对象模型一致。结果，当我尝试将 a 分配ComboBox给我的班级时，我收到一个错误（请参阅原始帖子），因为ComboBox不支持GotFocus()和LostFocus事件。

Now for the puzzle. If I add a ComboBox onto a worksheet (using Control ToolBox) and I double click that ComboBox to get to the code behind then all events are exposed, including GotFocus()and LostFocus()!

现在是拼图。如果我将 ComboBox 添加到工作表上（使用Control ToolBox），然后双击该 ComboBox 以访问背后的代码，则所有事件都会公开，包括GotFocus()和LostFocus()！

回答by Tim Williams

The below works for me. There were a couple of problem with your code, and comboboxes don't have a GotFocus event, so you'll have to use a different one. The collection needs to be a global in the module, noty part of the class. I couldn't get this to work using the generic "OLEobject" approach (same error you got).

以下对我有用。您的代码存在一些问题，并且组合框没有 GotFocus 事件，因此您必须使用不同的事件。集合需要是模块中的全局对象，而不是类的一部分。我无法使用通用的“OLEobject”方法（您遇到的错误相同）使其工作。

' ### in the class
Public WithEvents inputObj As MSForms.ComboBox

Private Sub inputObj_Change()
    MsgBox "Change!"
End Sub

' ### in a module
Dim tbCollection As Collection

Public Sub InitializePDRInput()
    Dim myObj As OLEObject
    Dim obj As clsPDRInput

    Set tbCollection = New Collection

    For Each myObj In Worksheets("Sheet1").OLEObjects
        If TypeName(myObj.Object) = "ComboBox" Then
            Set obj = New clsPDRInput
            Set obj.inputObj = myObj.Object
            tbCollection.Add obj
        End If
    Next myObj

End Sub

回答by ray

Update

更新

I was too focused in making the code compile and someone was nice enough to point out that the answer below is bad juju. So do not use. It does compile, but not a good answer.

我太专注于编译代码，有人很好地指出下面的答案是糟糕的 juju。所以不要使用。它确实可以编译，但不是一个好的答案。

---

---

I reproduced your error and fixed by changing the following declaration:

我重现了您的错误并通过更改以下声明进行了修复：

Public WithEvents inputObj As OLEObject

to this:

对此：

Public inputObj As New OLEObject

Of course, this is a different type of declaration so I'm not sure if it will work for you. It does remove the exception.

当然，这是一种不同类型的声明，所以我不确定它是否适合您。它确实删除了异常。

I'd also like to note that if you don't have Option Explicit set, you should. There are some variables in your code that are not declared. My guess is that you perhaps modified the code before posting your question.

我还要注意的是，如果您没有设置 Option Explicit，则应该设置。您的代码中有一些未声明的变量。我的猜测是您可能在发布问题之前修改了代码。

Just making sure.

只是确定。