Fire events for all comboboxes in my sheet
 

I know how to catch events for a particular combobox.

I wonder if there is some way to declare a generic event handler for ALL
comboboxes in my sheet/workbook.

TIA
--
AP

Fire events for all comboboxes in my sheet
 

One way -

'in a class named Class1
Public WithEvents cbo As MSForms.ComboBox

' add events from the top right dropdown
Private Sub cbo_Change()
'eg
Select Case cbo.Name
Case "ComboBox1": 'code
End Select

End Sub

'in a normal module

Dim colCBOcls As Collection

Sub SetCBOevents()
Dim oOLE As OLEObject
Dim cls As Class1
Set colCBOcls = New Collection
For Each oOLE In Worksheets("Sheet2").OLEObjects

If TypeName(oOLE.Object) = "ComboBox" Then
Set cls = New Class1
Set cls.cbo = oOLE.Object
colCBOcls.Add cls
End If
Next

End Sub

Perhaps run SetCBOevents from an Open event. Or from the sheet-activate
event and also from the deactivate event another routine to Set colCBOcls =
Nothing to destroy the class's.

Regards,
Peter T

"Ardus Petus" wrote in message
...
I know how to catch events for a particular combobox.

I wonder if there is some way to declare a generic event handler for ALL
comboboxes in my sheet/workbook.

TIA
--
AP

Fire events for all comboboxes in my sheet
 

Thanks a lot!
--
AP

"Peter T" <peter_t@discussions a écrit dans le message de news:
...
One way -

'in a class named Class1
Public WithEvents cbo As MSForms.ComboBox

' add events from the top right dropdown
Private Sub cbo_Change()
'eg
Select Case cbo.Name
Case "ComboBox1": 'code
End Select

End Sub

'in a normal module

Dim colCBOcls As Collection

Sub SetCBOevents()
Dim oOLE As OLEObject
Dim cls As Class1
Set colCBOcls = New Collection
For Each oOLE In Worksheets("Sheet2").OLEObjects

If TypeName(oOLE.Object) = "ComboBox" Then
Set cls = New Class1
Set cls.cbo = oOLE.Object
colCBOcls.Add cls
End If
Next

End Sub

Perhaps run SetCBOevents from an Open event. Or from the sheet-activate
event and also from the deactivate event another routine to Set colCBOcls
=
Nothing to destroy the class's.

Regards,
Peter T

"Ardus Petus" wrote in message
...
I know how to catch events for a particular combobox.

I wonder if there is some way to declare a generic event handler for ALL
comboboxes in my sheet/workbook.

TIA
--
AP