Working with UserForms in Excel 2016 VBA
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:
Activate the VBE by pressing Alt+F11.
Select the workbook that will hold the UserForm in the Project window.
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.
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|
|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|
|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.
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:
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:
Make sure that the correct control is selected in the UserForm.
Make sure the Properties window is visible.
In the Properties window, click the property that you want to change.
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() UserForm1.Show ‘ 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.