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

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） - “对象或类不支持set if if events”

In a class called clsPDRinput I 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 OLEObject is too generic and not every OLEObject supports the GotFocus() event and that is why the code is giving the error message?

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

I have tried replacing OLEObject with MSForms.ComboBox but that doesn't resolve issue.

我已经尝试用MSForms.ComboBox替换OLEObject，但这不能解决问题。

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

任何想法 - 现在用Google搜索了两个小时，然后空白......

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 events exposed are GotFocus() and LostFocus().
2. 如果将变量声明为OLEObject（如在... inputObj中作为OLEObject），则公开的唯一事件是GotFocus（）和LostFocus（）。
3. 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()) but the events GotFocus() and LostFocus() are not exposed
4. 如果将变量声明为MSForms.ComboBox（如在... inputObj中为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 ComboBox to my class I get an error (see original post) as the ComboBox does not support the GotFocus() and LostFocus events.

点1和点2与excel中的对象模型一致。因此，当我尝试将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（）！

2 个解决方案

#1

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

#2

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 set，你应该。您的代码中有一些未声明的变量。我的猜测是你可能在发布问题之前修改了代码。

Just making sure.

只是确定。