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

You need to have a click subroutine for each combobox. This routine can call
a common routine for processing all the comboboexes.. the routine can set a
variable with the combobox name. Excel will take care of calling the correct
routine for each combobox

Private Sub mcombobox1_Click()
BoxName = "mcombobox1"
call commonbox(BoxName)
end sub

Private Sub mcombobox2_Click()
BoxName = "mcombobox2"
call commonbox(BoxName)
end sub

Private Sub mcombobox3_Click()
BoxName = mcombobox3
call commonbox(BoxName)
end sub

"Phillip" wrote:

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

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

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

However I cannot see how to make all the comboboxes fire the event

All your comboboxes will trigger their withevents click events, however
without including separate variables for each class you can only trap the
single raiseevent in the form. IOW, you'd need additional Withevent
variables like cevent1, cevent2 etc.

Whilst you could do something like that it's convoluted and not necessary.
Either of the following should work much more simply

' in the class module

Public WithEvents mcombobox As msforms.ComboBox
' Public Event UpdateLabel(ControlNo As Long)
Public mLabel As msforms.Label
Public mkey As Long

Private Sub mcombobox_Click()
mLabel.Caption = mcombobox.Value
End Sub

'' in the form
' remove Public WithEvents cevent As

in the initialise event
Dim cevent As ClsComboEvent
' new line in the initialize event
Set cevent.mLabel = Me.Controls("Label" & mindex)


Alternatively -
' in the class
Private Sub mcombobox_Click()
UsrDemo.UpdateLabel mkey, mcombobox.Value
End Sub

' in the form
Public Sub UpdateLabel(idx As Long, sText As String)
Me.Controls("Label" & idx).Caption = sText
End Sub


Regards,
Peter T


"Phillip" wrote in message
...
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