I need to make it known when a combobox or optionbutton was clicked. This can be in two separate classes. What I have tried is this:
Code
Dim arrEvents As Collection
Private Sub Worksheet_Activate()
Dim objButtonEvents As ButtonEvents
Dim objButtEvents As ButtonEvents
Dim shp As Shape
Set arrEvents = New Collection
Set arr2Events = New Collection
For Each shpCursor In Me.Shapes
If shpCursor.Type = msoOLEControlObject Then
If TypeOf shpCursor.OLEFormat.Object.Object Is MSForms.CommandButton Then
Set objButtonEvents = New ButtonEvents
Set objButtonEvents.cmdButton = shpCursor.OLEFormat.Object.Object
arrEvents.Add objButtonEvents
End If
End If
If shpCursor.Type = msoOLEControlObject Then
If TypeOf shpCursor.OLEFormat.Object.Object Is MSForms.ComboBox Then
Set objButtEvents = New ButtonEvents
'Set objButtEvents.cboButton = shpCursor.OLEFormat.Object.Object
arr2Events.Add objButtEvents
End If
End If
Next
End Sub
The above is in a worksheet change section.
In the class module I have:
CODE
Public WithEvents cmdButton As MSForms.CommandButton
Public WithEvents cboButton As MSForms.ComboBox
Private Sub cmdButton_Click()
MsgBox cmdButton.Caption & " was pressed!"
End Sub
Private Sub cboButton_Click()
MsgBox cboButton.Name & " was pressed!"
End Sub
/CODE
What am I missing? Complete code examples will be appreciated!
Sorry I couldn't figure out how to add two code pieces to my question.
Display More