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
|