Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fire events for all comboboxes in my sheet
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ComboBox Events Fire Many Times | Excel Programming | |||
Workbook not closed when OnBeginShutdown and OnDisconnection events fire in Excel Addin | Excel Programming | |||
How do you fire HTML events using Excel to automate IE? | Excel Programming | |||
Which Events Fire When I Delete A Row, and How Many Times? | Excel Programming | |||
msg box and or sheet events | Excel Programming |