On 5 Aug, 10:28, "Peter T" <peter_t@discussions wrote:
In the loop you forgot to do this -
Set cevent.mComboBox = ctrlControl

That'll get your withevents class working. However, as written, "cevent"
will only trap your RaiseEvent in the last class you created, iow the
userform will only receive the event of the last combobox.

It all seems overly complicated, why not simply pass a reference of the
label associated with the combobox to the class module, do everything from
code in the withevents class.

Regards,
Peter T

PS No = CStr(Right(mcombobox.Name, 1))

that's not nice, pass an 'index' as a module level variable in the class,
and why CStr instead of Val or CLng.

"Phillip" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Peter T

Thanks for your help

I have made the change you suggested in the loop and made a module
level variiable in the class . As you said the Update Label event now
works only on the last combobox click event.
However I cannot see how to make all the comboboxes fire the event

Current code is

UsrDemo code

Public WithEvents cevent As clsComboEvent
Private Sub cevent_UpdateLabel(Num As Long)
Me.Controls("Label" & Num).Caption = Me.Controls("ComboBox" &
Num).Text
End Sub

Private Sub UserForm_Initialize()
Dim ctrlControl As MSForms.control
Dim mindex As Long
Set mColEvents = New Collection
mindex = 1
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
Set cevent.mcombobox = ctrlControl
cevent.mkey = mindex
mColEvents.Add Item:=cevent, key:=CStr(mindex)
mindex = mindex + 1
End If
Next
End Sub


ClsComboevent code

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

Private Sub mcombobox_Click()
RaiseEvent UpdateLabel(mkey) 'pass the last digit of the control name
End Sub