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:

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 F5 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
Userform Initialize Event

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

    '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

    '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

Show advanced topics

Displaying the UserForm

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

    Sub ShowForm()


    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:

Button Without Macro

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 is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

© 2023 SuperExcelVBA | ABOUT

Protected by Copyscape