UserForm Event

---

Once the UserForm layout has been completed, you must configure its settings so that the options that the user has in each control have functionality. To do this, we will use the events of the UserForm.

In this way we can:

• Change the layout of the UserForm more dynamically (E.g. fill in the lists of the combo boxes or the list boxes , empty the text boxes , uncheck the option buttons , etc).
• Run VBA macros and codes from parameters and a friendly interface

---

Adding an Event

To customize the various controls (E.g. combo boxes) from the beginning of using UserForm we will use the Initialize event. This event executes the Sub code every time the UserForm is loaded into Excel memory (for example when it enters the view with or ).

To create a startup event:

1. Right-click the UserForm
2. Select View Code
3. In the left Dropdown menu select UserForm
4. In the right Dropdown menu select Initialize

If there is no Sub in the Code Window, manually add the event described in the code below:

Private Sub UserForm_Initialize()

End Sub

The VBA may have created the event Private SubUserForm_Click() automatically, delete it if you want (in this case we are setting Initialize and not Click).

By default the Subs created automatically through the VBE menus are Private. Change to Public Sub if you want to access them not only by UserForm.

---

Configuring the Initialize Event

Make sure that the name of the controls used in the Initialize event are the same as those created for the UserForm layout. If so, we can add the following code (to add functionality to the previous example of Userform Layout):

Private Sub UserForm_Initialize()

    'Clears the text box
    TextBox1.Value = ""

    'Fills in the initial age
    TextBox2.Value = 30

    'Sets the initial age
    SpinButton1.Value = 30

    'Clears the option buttons
    OptionButton1.Value = False
    OptionButton2.Value = False
    OptionButton3.Value = False

    'Clears the combo box
    ComboBox1.Clear

    'Fills in the combo box
    With ComboBox1
        .AddItem "Purchasing Department"
        .AddItem "Finance Department"
        .AddItem "Legal Department"
        .AddItem "Planning Department"
        .AddItem "Human Resources Department"
        .AddItem "IT Department"
        .AddItem "Sales Department"
        .Style = 2 'Prevents the user from entering an input that is not on the list
    End With

    'Clears the list box
    ListBox1.Clear

    'Populate the list box
    With ListBox1
        .AddItem "Downtown"
        .AddItem "Historic District"
        .AddItem "Chinatown"
    End With

    'Clears the check boxes
    CheckBox1.Value = False
    CheckBox2.Value = False

End Sub

---

---

Displaying the UserForm

To display the UserForm you need to run a macro like the following:

    Sub ShowForm()

        UserForm1.Show

    End Sub

This macro, inserted in a , can be assigned to a button in a worksheet, or run in response to a user triggered event.

Note that after setting the Initialize event, if the UserForm is called (Show) the button options will already be available:

Although the initial control options have been configured, still more settings are required to the operation of all functionalities of the form (E.g.: command buttons, spin buttons, etc.).

---

vbModal or vbModeless

When executing the Show command and displaying the UserForm, the worksheet is usually locked for editing, however we can change it with parameterization.

    Sub ShowForm()

        UserForm1.Show 0 'vbModeless

    End Sub 

Configuration	Value	Description
vbModal	1	UserForm is a modal window. Default option.
vbModeless	0	UserForm is not a modal window

---

• Previous page
• Next page

---

• report error/feedback