Working with UserForms in Excel 2016 VBA - dummies

Working with UserForms in Excel 2016 VBA

By John Walkenbach

Each Excel dialog box that you create in VBA is stored in its own UserForm object — one dialog box per UserForm. You create and access these UserForms in the Visual Basic Editor.

Inserting a new UserForm

Insert a UserForm object by following these steps:

  1. Activate the VBE by pressing Alt+F11.

  2. Select the workbook that will hold the UserForm in the Project window.

  3. Choose Insert  →  UserForm.

    The VBE inserts a new UserForm object, which contains an empty dialog box.

Here is a UserForm — an empty dialog box. Your job, if you choose to accept it, is to add some controls to this UserForm.

A new UserForm object.
A new UserForm object.

Adding controls to a UserForm

When you activate a UserForm, the VBE displays the Toolbox in a floating window. You use the tools in the Toolbox to add controls to your UserForm. If, for some reason, the Toolbox doesn’t appear when you activate your UserForm, choose View  →  Toolbox.

To add a control, just click the desired control in the Toolbox and drag it into the dialog box to create the control. After you add a control, you can move and resize it by using standard techniques.

Here is a list of the various tools, as well as their capabilities.

Control What It Does
Label Shows text
TextBox Allows the user to enter text
ComboBox Displays a drop-down list
ListBox Displays a list of items
CheckBox Provides options such as on/off or yes/no
OptionButton Allows the user to select one of several options; used in
groups of two or more
ToggleButton Enables the user to switch a button on or off
Frame Contains other controls
CommandButton A clickable button
TabStrip Displays tabs
MultiPage A tabbed container for other objects
ScrollBar Enables the user to drag a bar to establish a setting
SpinButton Enables the user to click a button to change a value
Image Holds an image
RefEdit Allows the user to select a range

Changing properties for a UserForm control

Every control you add to a UserForm has properties that determine how the control looks or behaves. In addition, the UserForm itself has its own set of properties. You can change these properties with the aptly named Properties window. This shows the Properties window when a CommandButton control is selected.

Use the Properties windows to change the properties of UserForm controls.
Use the Properties windows to change the properties of UserForm controls.

The Properties window appears when you press F4, and the properties shown in this window depend on what is selected. If you select a different control, the properties change to those appropriate for that control. To hide the Properties window and get it out of the way, click the Close button in its title bar. Pressing F4 always brings it back when you need it.

Properties for controls include the following:

  • Name

  • Width

  • Height

  • Value

  • Caption

Each control has its own set of properties (although many controls have some common properties). To change a property using the Properties window, follow these steps:

  1. Make sure that the correct control is selected in the UserForm.

  2. Make sure the Properties window is visible.

  3. In the Properties window, click the property that you want to change.

  4. Make the change in the right portion of the Properties window.

If you select the UserForm itself (not a control on the UserForm), you can use the Properties window to adjust UserForm properties.

Viewing the UserForm Code window

Every UserForm object has a Code module that holds the VBA code (the event-handler procedures) that is executed when the user works with the dialog box. To view the Code module, press F7. The Code window is empty until you add some procedures. Press Shift+F7 to return to the dialog box.

Here’s another way to switch between the Code window and the UserForm display: Use the View Code and View Object buttons in the Project window’s title bar. Or right-click the UserForm and choose View Code. If you’re viewing code, double-click the UserForm name in the Project window to return to the UserForm.

Displaying a UserForm

You display a UserForm by using the UserForm’s Show method in a VBA procedure.

The macro that displays the dialog box must be in a VBA module — not in the Code window for the UserForm.

The following procedure displays the dialog box named UserForm1:

Sub ShowDialogBox()
‘  Other statements can go here
End Sub

When Excel displays the dialog box, the ShowDialogBox macro halts until the user closes the dialog box. Then VBA executes any remaining statements in the procedure. Most of the time, you won’t have any more code in the procedure. As you later see, you put your event-handler procedures in the Code window for the UserForm. These procedures kick in when the user works with the controls on the UserForm.

Using information from a UserForm

The VBE provides a name for each control you add to a UserForm. The control’s name corresponds to its Name property. Use this name to refer to a particular control in your code. For example, if you add a CheckBox control to a UserForm named UserForm1, the CheckBox control is named CheckBox1 by default. You can use the Properties box to make this control appear with a check mark. Or you can write code to do it:

UserForm1.CheckBox1.Value = True

Most of the time, you write the code for a UserForm in the UserForm’s code module. If that’s the case, you can omit the UserForm object qualifier and write the statement like this:

CheckBox1.Value = True

Your VBA code can also check various properties of the controls and take appropriate actions. The following statement executes a macro named PrintReport if the check box (named CheckBox1) is checked:

If CheckBox1.Value = True Then Call PrintReport

It’s usually a good idea to change the default name the VBE has given to your controls to something more meaningful.