如何使用excel vba将事件分配给多个对象？ [英] How to assign an event to multiple objects with excel vba?

问题描述

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

我写了以下代码，但我遇到运行时错误（459） - 对象或类不支持set if events

在一个名为 clsPDRinput 的类中，我有以下：

  Public WithEvents inputObj As OLEObject 
 
公共属性设置myInput（obj As OLEObject）
设置inputObj = obj 
结束属性
 
 Public Sub tbPDRInput_GotFocus（）
 //做一些事情... 
 End Sub 
  

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

  Dim tbCollection As Collection 
 
 Public Sub InitializePDRInput（）
 Dim myObj As OLEObject 
 Dim obj As clsPDRInput 
 
设置tbCollection =新集合
对于每个myObj工作表（1. PDR文档）OLEObjects 
如果TypeName（myObj.Object）=ComboBoxthen 
 Set obj = new clsPDRInput 
 Set obj.myInput = myObj& **此行错误** 
 tbCollection.Add obj 
结束如果
下一个myObj 
设置obj =无任何
结束子
  

我不知道是什么原因导致这个错误。一个虽然我有， OLEObject 是太泛型，并不是每个 OLEObject 支持 GotFocus ）事件，这就是为什么代码给出错误消息？

我已经尝试替换 OLEObject 与 MSForms.ComboBox 但是没有解决问题。

任何想法 - 现在有两个小时了...

EDIT - 更新我认为问题是...

1. 如果你声明一个变量为 OLEObject （如 ... inputObj as OLEObject ），那么 暴露的是 GotFocus（）和 LostFocus（）。


2. 如果你声明一个变量为 MSForms.ComboBox （如 ... inputObj as MSForms.ComboBox ），暴露各种事件（例如 Change（）， Click（）， DblClick ）（） c> > 不暴露



点1和点2与excel中的对象模型一致。因此，当我尝试为我的类分配一个 ComboBox 时，我得到一个错误（见原始帖子）为 ComboBox 不支持 GotFocus（）和 LostFocus 事件。

现在的拼图。如果我添加一个ComboBox到工作表（使用 Control ToolBox ），我双击该ComboBox获取到后面的代码，所有事件，包括 GotFocus（）和 LostFocus（）！

解决方案

以下适用于我。你的代码有一些问题，组合框没有GotFocus事件，所以你必须使用不同的。集合需要在模块中是全局的，而不是类的一部分。我不能得到这个工作使用通用的OLEobject方法（同样的错误你得到）。

 '### in the class 
 Public WithEvents inputObj As MSForms.ComboBox 
 
 Private Sub inputObj_Change（）
 MsgBox更改！ 
 End Sub 
 
'### in a module 
 Dim tbCollection As Collection 
 
 Public Sub InitializePDRInput（）
 Dim myObj As OLEObject 
 Dim obj As clsPDRInput 
 
设置tbCollection =新集合
 
对于每个myObj在工作表（Sheet1）。OLEObjects 
如果TypeName（myObj。 Object）=ComboBoxthen 
 Set obj = new clsPDRInput 
设置obj.inputObj = myObj.Object 
 tbCollection.Add obj 
结束如果
下一个myObj 
 
 End Sub 
  

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

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

In a class called clsPDRinput I have the following:

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?

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

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 events exposed 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()) but the events GotFocus() and LostFocus() are not exposed

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.

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()!

解决方案

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).

' ### 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

这篇关于如何使用excel vba将事件分配给多个对象？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！