I’ve been fooling around with UserForms lately and have a couple of posts in mind. This one describes a UserForm event class that handles more than one type of form control.
There’s good explanations on the web for creating arrays of userform controls that handle all the events for a certain type of control, like a TextBox. That way, for example, you don’t have to duplicate the click event for each TextBox. In this post I create a single event-handler class for multiple types of controls: CheckBoxes, ComboBoxes, OptionButtons, and the like. This way you don’t need to create a separate class for each type of control – one class can handle them all.
(If the idea of an array of event-handler classes is new to you, please click the link above. John Walkenbach has a nice example too. The very brief explanation is that you create an array of classes, one for each control, which then handle whatever control events you specify in the class. Note that I use a collection instead of an array. That’s just how I learned it.)
The class in this example, called “clsMultipleControls,” handles the click or change events for CheckBoxes, ComboBoxes, ListBoxes, OptionButton, SpinButtons and TextBoxes. It has one property, called “PassedControl,” with an associated class-level variable, “m_PassedControl.” When m_Passed_Control is set, the code determines its type and assigns it to the appropriate WithEvents control. So, if the passed control is a TextBox the “txt” variable is set to m_Passed_Control. The last routine just prints the control’s name, as a stand-in for the real work that your class could do.
Private WithEvents chk As MSForms.CheckBox
Private WithEvents cbo As MSForms.ComboBox
Private WithEvents lst As MSForms.ListBox
Private WithEvents opt As MSForms.OptionButton
Private WithEvents spn As MSForms.SpinButton
Private WithEvents txt As MSForms.TextBox
Property Set ctl(PassedControl As MSForms.Control)
Set m_PassedControl = PassedControl
Select Case TypeName(PassedControl)
Case "CheckBox"
Set chk = PassedControl
Case "ComboBox"
Set cbo = PassedControl
Case "ListBox"
Set lst = PassedControl
Case "OptionButton"
Set opt = PassedControl
Case "SpinButton"
Set spn = PassedControl
Case "TextBox"
Set txt = PassedControl
End Select
End Property
Private Sub cbo_Change()
PrintControlName
End Sub
Private Sub chk_Click()
PrintControlName
End Sub
Private Sub lst_Change()
PrintControlName
End Sub
Private Sub opt_Click()
PrintControlName
End Sub
Private Sub spn_Change()
PrintControlName
End Sub
Private Sub txt_Change()
PrintControlName
End Sub
Sub PrintControlName()
Debug.Print m_PassedControl.Name
End Sub
The event routines above are just a sample. You can use as many events as are supported by that type of control. For example, a ComboBox supports the click, DropButtonClick, KeyUp and Mousedown events, among others. One limitation is that WithEvents controls don’t support Exit and Enter (and some other) events, as those are actually generated at the Userform level. To see which events are supported by a certain type of control, use the two dropdowns at the top of the class module:
One thing to note above is that I used the “TypeName” function rather than something like
. One reason is that you can’t seem to use TypeOf in a Select Case statement. The second is that some controls return True for multiple types. For example, the OptionButton passes both “Is MSForms.OptionButton” and “Is MSForms.CheckBox.” I assume this is because the OptionButton is based on the Checkbox.
Here’s the initialization code in the UserForm. As promised, it’s very simple.
Private cMultipleControls As clsMultipleControls
Private Sub UserForm_Activate()
Dim ctl As MSForms.Control
Set collControls = New Collection
For Each ctl In Me.Controls
Set cMultipleControls = New clsMultipleControls
Set cMultipleControls.ctl = ctl
collControls.Add cMultipleControls
Next ctl
End Sub
This code establishes a collection of instances of clsMultipleControls, one for each control in the form. Each class instance has an instantiated m_Passed_Control variable, and no more than one instantiated WithEvents control. I say “no more than one” because with this setup, although a CommandButton would generate a class, there’s no WithEvents CommandButton variable, so it wouldn’t be identified in the class’s
subroutine.
I suppose this might be seen as inefficient, stuffing all these possibilities into a single class. But I like the way it simplifies the form coding and the flexibility of the class. What do you think?
In the next post, I’ll expand this class a bit and demonstrate what I think is a nice use for this type of class. I’ll also show a situation where TypeOf is required (I think), the problem that creates and the solution I came up with.
Meanwhile, here’s a downloadable workbook with the UserForm and class.
Hi Doug,
Great article and found it really helpful! I have been playing around with this code, I just seem to run into an issue with this code when I want to have this applied to objects I created procedurally.
For instance i created a UserForm with a ListBox and a TextBox. before initializing the UserForm I add another ListBox and TextBox (and give them the name ListBox2 and TextBox2). Now when I change selection in the 1st ListBox or enter something in the TextBox, an event is raised. However on the 2 procedurally created ones nothing happens… Any ideas on that?
This is the code i use to generate the objects and launch the UserForm:
Dim newLB As msforms.ListBox
Dim newTB As msforms.TextBox
UserForm1.ListBox1.AddItem "number 1"
UserForm1.ListBox1.AddItem "number 2"
UserForm1.ListBox1.AddItem "number 3"
Set newLB = UserForm1.Controls.Add("Forms.Listbox.1", "Listbox2")
UserForm1!ListBox2.Left = UserForm1.ListBox1.Width + 50
UserForm1!ListBox2.Top = UserForm1.ListBox1.Top
UserForm1!ListBox2.Width = UserForm1.ListBox1.Width
UserForm1!ListBox2.Height = UserForm1.ListBox1.Height
UserForm1!ListBox2.AddItem "number 1"
UserForm1!ListBox2.AddItem "number 2"
UserForm1!ListBox2.AddItem "number 3"
Set newTB = UserForm1.Controls.Add("Forms.textbox.1", "textbox2")
UserForm1!TextBox2.Left = UserForm1.TextBox1.Width + 50
UserForm1!TextBox2.Top = UserForm1.TextBox1.Top
UserForm1!TextBox2.Width = UserForm1.TextBox1.Width
UserForm1!TextBox2.Height = UserForm1.TextBox1.Height
UserForm1.Show False
End Sub
Much appreciated and regards,
Maurice
Hey Maurice, thanks!
There’s quite a few answers out there about this. Here’s one: http://stackoverflow.com/questions/3014421/how-to-add-events-to-controls-created-at-runtime-in-excel-with-vba. If that doesn’t work try searching “excel vba withevents runtime control”.
You could also ask a question at Stack Overfow or another forum. The basic process is the same, you just have to add the newly created control to the class.
Sorry, I don’t have time to do more than this. Good luck.
Neat post, Doug. Still warm, after nearly a year.
Thanks for this helpful post