10. Userforms: An Introduction

User Interaction Methods

Userforms enable you to display information and allow the user to input information. InputBox and MsgBox controls are simple ways of doing this. You can use the userform controls in the VB Editor to create forms that are more complex.

This chapter covers simple user interfaces using input boxes and message boxes and the basics of creating userforms in the VB Editor.

Input Boxes

The InputBox function is used to create a basic interface element that requests input from the user before the program can continue. You can configure the prompt, the title for the window, a default value, the window position, and user help files. The only two buttons provided are the OK and Cancel buttons. The returned value is a string.

The following code asks the user for the number of months to be averaged. Figure 10.1 shows the resulting InputBox.

AveMos = InputBox(Prompt:="Enter the number " & _
" of months to average", Title:="Enter Months", _
Default:="3")

Figure 10.1. A simple but effective input box.

Message Boxes

The MsgBox function creates a message box that displays information and waits for the user to click a button before continuing. Whereas InputBox only has OK and Cancel buttons, the MsgBox function allows you to choose from several configurations of buttons, including Yes, No, OK, and Cancel. You can also configure the prompt, the window title, and help files. The following code produces a prompt to find out whether the user wants to continue. A Select Case statement is then used to continue the program with the appropriate action. Figure 10.2 shows the resulting customized message box.

Figure 10.2. The MsgBox function is used to display information and obtain a basic response from the user.

Creating a Userform

Userforms combine the capabilities of InputBox and MsgBox to create a more efficient way of interacting with the user. For example, rather than have the user fill out personal information on a sheet, you can create a userform that prompts for the required data (see Figure 10.3).

Figure 10.3. Create a custom userform to get more information from the user.

Insert a userform in the VB Editor by selecting Insert, UserForm from the main menu. When a UserForm module is added to the Project Explorer, a blank form appears in the window where your code usually is, and the Controls toolbox appears.

You can resize the form by grabbing and dragging the handles on the right side, bottom edge, or lower-right corner of the userform. To add controls to the form, click the desired control in the toolbox and draw it on the form. Controls can be moved and resized at any time.

After a control is added to a form, its properties can be changed from the Properties window. These properties can be set manually now or set later programmatically. If the Properties window is not visible, you can bring it up by selecting View, Properties Window. Figure 10.4 shows the Properties window for a text box.

Figure 10.4. Use the Properties window to change the properties of a control.

Calling and Hiding a Userform

A userform can be called from any module. FormName.Show pops up a form for the user:

frm_AddEmp.Show

The Load method can also be used to call a userform. This allows a form to be loaded but remain hidden:

Load frm_AddEmp

To hide a userform, use the Hide method. The form is still active but hidden from the user. However, the controls on the form can still be accessed programmatically:

Frm_AddEmp.Hide

The Unload method unloads the form from memory and removes it from the user’s view, which means the form cannot be accessed by the user or programmatically:

Unload Me

Programming the Userform

The code for a control goes in the Forms module. Unlike the other modules, double-clicking the Forms module opens up the form in Design view. To view the code, you can right-click either the module or the userform in Design mode and select View Code.

Userform Events

Just like a worksheet, a userform has events triggered by actions. After the userform has been added to the project, the events are available in the Properties drop-down list at the top-right of the code window (see Figure 10.5) by selecting UserForm from the Objects drop-down on the left.

Figure 10.5. Various events for the userform can be selected from the drop-down list at the top of the code window.

The available events for userforms are described in Table 10.1.

Table 10.1. The Events for Userforms

Programming Controls

To program a control, highlight the control and select View, Code. The footer, header, and default action for the control is entered in the programming field automatically. To see the other actions that are available for a control, select the control from the Object drop-down and view the actions in the Properties drop-down, as shown in Figure 10.6.

Figure 10.6. Various actions for a control can be selected from the VB Editor drop-downs.

The controls are objects, like ActiveWorkbook. They have properties and methods, dependent on the type of control. Most of the programming for the controls is done behind the form. However, if another module needs to refer to a control, the parent, which is the form, needs to be included with the object. Here’s how this is done:

Private Sub btn_EmpCancel_Click()
Unload Me
End Sub

The preceding code can be broken down into three sections:

• btn_EmpCancel—Name given to the control

• Click—Action of the control

• Unload Me—The code behind the control, which, in this case, is unloading the form)

Using Basic Form Controls

Each control has different events associated with it, which allows you to code what happens based on the user’s actions. A table reviewing the control events is available at the end of each of the sections that follow.

Using Labels, Text Boxes, and Command Buttons

The basic form shown in Figure 10.7 consists of labels, text boxes, and command buttons. It is a simple yet effective method of requesting information from the user. After the text boxes have been filled in, the user clicks OK, and the information is added to a sheet (see Figure 10.8).

Figure 10.7. A simple form to collect information from the user.

Figure 10.8. The information is added to the sheet.

With a change in the code shown in the following sample, the same form design can be used to retrieve information. The following code retrieves the position and hire date after the employee’s name is entered:

The available events for Label, TextBox, and CommandButton controls are described in Table 10.2.

Table 10.2. The Events for Label, TextBox, and CommandButton Controls

Deciding Whether to Use List Boxes or Combo Boxes in Forms

You can let users type in an employee name to search for, but what if they misspell the name? You need a way to make sure that the name is typed correctly. Which do you use: a list box or a combo box?

• A list box displays a list of values from which the user can choose.

• A combo box displays a list of values from which the user can choose and allows the user to enter a new value.

In this case, when you want to limit user options, you should use a list box to list the employee names, as shown in Figure 10.9.

Figure 10.9. Use a list box to control user input.

In the RowSource property of the list box, enter the range from which the control should draw its data. Use a dynamic named range to keep the list updated if employees are added, as shown in the following code:

Using the MultiSelect Property of a List Box

List boxes have a MultiSelect property, which allows the user to select multiple items from the choices in the list box, as shown in Figure 10.10:

• fmMultiSelectSingle—The default setting allows only a single item selection at a time.

• fmMultiSelectMulti—Allows an item to be deselected by clicking it again; multiple items can also be selected.

• fmMultiSelectExtended—Allows the Ctrl and Shift keys to be used to select multiple items.

Figure 10.10. MultiSelect allows the user to select multiple items from a list box.

If multiple items are selected, the Value property cannot be used to retrieve the items. Instead, check to see whether the item is selected, and then manipulate it as needed using the following code:

The items in a list box start counting at zero. For this reason, if you use the ListCount property, you must subtract one from the result:

For i = 0 To lb_EmpPosition.ListCount – 1

The available events for ListBox controls and ComboBox controls are described in Table 10.3.

Table 10.3. Events for ListBox and ComboBox Controls

Adding Option Buttons to a Userform

Option buttons are similar to check boxes in that they can be used to make a selection. However, unlike check boxes, option buttons can be configured to allow only one selection out of a group.

Using the Frame tool, draw a frame to separate the next set of controls from the other controls on the userform. The frame is used to group option buttons together, as shown in Figure 10.11.

Figure 10.11. Use a frame to group option buttons together.

Option buttons have a GroupName property. If you assign the same group name, Buildings, to a set of option buttons, you force them to act collectively as a toggle, so that only one button in the set can be selected. Selecting an option button automatically deselects the other buttons in the same group or frame. To prevent this behavior, either leave the GroupName property blank or enter another name.

The available events for OptionButton controls and Frame controls are described in Table 10.4.

Table 10.4. Events for OptionButton and Frame Controls

Adding Graphics to a Userform

A listing on a form can be even more helpful if a corresponding graphic is added to the form. The following code displays the photograph corresponding to the selected employee from the list box:

The available events for Graphic controls are described in Table 10.5.

Table 10.5. Events for Graphic Controls

Using a Spin Button on a Userform

As it is, the Hire Date field allows the user to enter the date in any format including 1/1/1 or January 1, 2001. This possible inconsistency can create problems later on if you need to use or search for dates. The solution? Force users to enter dates in a unified manner.

Spin buttons allow the user to increment/decrement through a series of numbers. In this way, the user is forced to enter numbers rather than text.

Draw a spin button for a Month entry on the form. In the Properties, set the Min to 1 for January and the Max to 12 for December. In the Value property, enter 1, the first month. Next, draw a text box next to the spin button. This text box reflects the value of the spin button. In addition, labels can be used.

Private Sub SpBtn_Month_Change()
tb_Month.Value = SpBtn_Month.Value
End Sub

Finish building the form. Use a Min of 1 and Max of 31 for days or a Min of 1900 and a Max of 2100 for Year:

The available events for SpinButton controls are described in Table 10.6.

Table 10.6. Events for SpinButton Controls

Using the MultiPage Control to Combine Forms

The MultiPage control provides a neat way of organizing multiple forms. Instead of having a form for personal employee information and one for on-the-job information, combine the information into one multipage form, as shown in Figures 10.12 and 10.13.

Figure 10.12. Use the MultiPage control to combine multiple forms. This is the first page of the form.

Figure 10.13. This is the second page of the form.

You can modify a page by right-clicking the tab of the page, which displays the following menu of options: New Page, Delete Page, Rename, or Move.

Unlike many of the other controls where the Value property holds a user-entered or selected value, the Value property of the MultiPage control holds the number of the active page, starting at zero. For example, if you have a five-page form and want to activate the fourth page, do this:

MultiPage1.Value = 3

If you have a control you want all the pages to share, such as the Save or Cancel buttons, place the control on the main userform rather than on the individual pages, as shown in Figure 10.14.

Figure 10.14. Place common controls like the Close button on the main userform.

The available events for MultiPage controls are described in Table 10.7.

Table 10.7. Events for the MultiPage Control

Verifying Field Entry

Even if users are told to fill in all the fields, there is no way to force them to do so—except with an electronic form. As a programmer, you can ensure that all required fields are filled in by not allowing the user to continue until all requirements are met. Here’s how to do this:

Illegal Window Closing

The userforms created in the VB Editor are not that different from normal windows: They also include the X close button in the upper-right corner. Although using the button is not wrong, it can cause problems, depending on the objective of the userform. In cases like this, you might want to control what happens if the user presses the button. Use the QueryClose event of the userform to find out what method is used to close the form and code an appropriate action:

After you know the method the user used to try to close the form, you can create a message box similar to Figure 10.15 to warn the user that the method was illegal.

Figure 10.15. Control what happens when the user clicks the X button.

The QueryClose event can be triggered in four ways:

• vbFormControlMenu—The user right-clicks either on the form’s title bar and selects the Close command or clicks the X in the upper-right corner of the form..

• vbFormCode—The Unload statement is used.

• vbAppWindows—Windows shuts down.

• vbAppTaskManager—The application is shut down by the Task Manager.

Getting a Filename

One of the most common client interactions is when you need the client to specify a path and filename. Excel VBA has a built-in function to display the File Open dialog box, as shown in Figure 10.16. The client browses to and selects a file. When the client selects the Open button, instead of opening the file, Excel VBA returns the full path and filename to the code.

Figure 10.16. Use the File Open dialog box to allow the user to select a file.

The preceding code allows the client to select one file. If you want them to specify multiple files, use this code:

In a similar fashion, you can use Application.GetSaveAsFileName to find the path and filename that should be used to save a file.

Next Steps

Now that you have seen how to work with userforms, the next chapter examines charts. In Chapter 11, you learn how spreadsheet charting has become a highly customizable resource capable of handling large amounts of data.