UserForm Event Class – Multiple Control Types

UserForm Event Class – Multiple Control Types

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 m_PassedControl As MSForms.Control
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:

WithEvents event types

One thing to note above is that I used the “TypeName” function rather than something like

TypeOf ctl Is MSForms.ComboBox

. 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.

Public collControls As Collection
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

Set ctl

subroutine.

UserForm in action

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.

3 thoughts on “UserForm Event Class – Multiple Control Types

  1. 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:

    Sub LoadForm()

    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

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>