Thread
:
Fire events for all comboboxes in my sheet
View Single Post
#
3
Posted to microsoft.public.excel.programming
Ardus Petus
external usenet poster
Posts: 718
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
Reply With Quote
Ardus Petus
View Public Profile
Find all posts by Ardus Petus