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

Question

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

If you declare a variable as OLEObject (as in ...inputObj as OLEObject) then the only events exposed are GotFocus() and LostFocus().
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()!

`For Each myObj` ... `Next inputObj` is inconsistent and wouldn't compile, I think. Is this your actual code? — Jean-François Corbett, Jun 18 '11 at 11:26
Also, `Dim tbCollection As Collection` is in your class, but you use `tbCollection` in `Sub InitializePDRInput`, which I presume is in a module. What's going on here? — Jean-François Corbett, Jun 18 '11 at 11:31
@Jean-Francois Corbett - On your first point, this is cut down version of the code. The `For each..` issue you point is just a typo (i'll edit the post). As for point 2, again, typo - `Dim tbCollection...` is in a module — Alex P, Jun 18 '11 at 13:06

Tim Williams · Answer 1 · 2021-11-27T09:08:18.577

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, not 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

Thanks for the answer. Your code works (so +1) but I cannot use it as I need the `GotFocus()` event — Alex P, Jun 18 '11 at 14:00

ray · Answer 2 · 2011-06-18T15:03:06.487

0

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.

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.

Just making sure.

edited Jun 18 '11 at 15:03

answered Jun 17 '11 at 19:37

ray

8,521
7
44
58

`Public inputObj As New OLEObject`: This is called an auto-instancing variable. When the variable `inputObj` is first encountered in the code, a new instance is created. In general, you should avoid auto-instancing variables for two reasons: First, it adds overhead to the code because the variable must be tested for `Nothing` every time it is encountered in code. – Jean-François Corbett Jun 18 '11 at 11:20
Second, you have no way to test whether an auto-instancing variable is `Nothing` because the very act of using the variable name in an `If Obj Is Nothing Then` statement will automatically create an instance of the variable. [source](http://www.cpearson.com/excel/classes.aspx) – Jean-François Corbett Jun 18 '11 at 11:21
@Jean-François Corbett I did not know; thanks for pointing it out. – ray Jun 18 '11 at 15:00

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

2 Answers2

Linked