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.

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

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