I am working on a project with several userform multipage controls
Each page has many comboboxes and a corresponding label control

If the user clicks the third value in the combobox then I want the
corresponding label caption to equal the combo choice.

It is easy to code this using the combobox click event, but I want to
reduce the amount of code,so my preferred solution is for a single
combobox event in a class. The class has an event statement and a
raise event in the class combo click event runs the updatelabel event
in the userform to update the label caption for the corresponding
combobox

I stored each combox contol reference into a userform public
collection object
I do not want to use the method used by John Walkenbach, but use a
collection object instead.

I set up a demo userform to test the code, the userform is named
Usrdemo with 6 comboboxes and 6 labels. ComboBox1 to Combox6 Label1 to
Label6

The Class module is named clsComboEvent

My problem is that I cannot see how to get the RaiseEvent to work and
recognise which combo was clicked and also I cannot see how to get the
collection object recognised in the class

Here is my code


UsrDemo code

Public mColEvents As Collection
Public WithEvents cevent As clsComboEvent

Private Sub cevent_UpdateLabel(Num As Long)
MsgBox mColEvents(Num).key 'display clicked combobox name
End Sub

Private Sub UserForm_Initialize()
Dim ctrlControl As MSForms.control
Set mColEvents = New Collection
For Each ctrlControl In Me.Controls
If TypeOf ctrlControl Is MSForms.ComboBox Then
ctrlControl.AddItem "Red"
ctrlControl.AddItem "Green"
ctrlControl.AddItem "Blue"
ctrlControl.AddItem "Yellow"
ctrlControl.AddItem "Pink"
ctrlControl.AddItem "Orange"
Set cevent = New clsComboEvent
cevent.key = ctrlControl.Name
mColEvents.Add Item:=cevent, key:=ctrlControl.Name
End If
Next
End Sub


ClsComboEvent code

Public WithEvents mcombobox As MSForms.ComboBox
Public Event UpdateLabel(ControlNo As Long)
Dim mkey as string


Private Sub mcombobox_Click()
Dim No As Long
No = CStr(Right(mcombobox.Name, 1))
RaiseEvent UpdateLabel(No) 'pass the last digit of the control name
End Sub

Public Property Let key(CKey As String)
mkey = CKey
End Property
Public Property Get key() As String
key = mkey
End Property