In order to create a group events you'll need a custom class to capture the events ( ObjectListener
), public variable to keep the class references alive (usually a collection or array - ComboListener
) and a Macro to fill the collection ( AddListeners_ComboBoxes
).
Call the AddListeners_ComboBoxes
Macro from the Workbook_Open()
. You will need call AddListeners_ComboBoxes
again if the code breaks.
Standard Module
Public ComboListener As Collection
Sub AddListeners_ComboBoxes()
Dim ws As Worksheet
Dim obj As OLEObject
Dim listener As ObjectListener
Set ComboListener = New Collection
For Each ws In Worksheets
For Each obj In ws.OLEObjects
Select Case TypeName(obj.Object)
Case "ComboBox"
Set listener = New ObjectListener
Set listener.Combo = obj.Object
ComboListener.Add listener
End Select
Next
Next
End Sub
![enter image description here](https://i.stack.imgur.com/UxVMi.png)
Class ObjectListener
Option Explicit
Public WithEvents Combo As MSForms.ComboBox
Private Sub Combo_Change()
MsgBox Combo.Name
Select Case Combo.Name
Case "ComboBox2"
ActiveSheet.OLEObjects("ComboBox3").Object.ListIndex = 1
End Select
End Sub