Chapter 13: Introducing UserForms

IN THIS CHAPTER

• Creating, showing, and unloading UserForms

• Exploring the UserForm controls available to you

• Setting the properties of UserForm controls

• Controlling UserForms with VBA procedures

• Creating a UserForm

• Introducing the types of events relevant to UserForms and controls

• Customizing your control Toolbox

• Going over a handy checklist for creating UserForms

How Excel Handles Custom Dialog Boxes

Excel makes creating custom dialog boxes for your applications relatively easy. In fact, you can duplicate the look and feel of many of Excel's dialog boxes.

Excel developers have always had the ability to create custom dialog boxes for their applications. Beginning with Excel 97, things changed substantially — UserForms replaced the clunky old dialog sheets. UserForms are much easier to work with, and they offer many additional capabilities. Even though UserForms haven't been upgraded over the years, you'll find that this feature works well and is very flexible.

A custom dialog box is created on a UserForm, and you access UserForms in the Visual Basic Editor (VBE).

Following is the typical sequence that you'll follow when you create a UserForm:

1. Insert a new UserForm into your workbook's VB Project.

2. Add controls to the UserForm.

3. Adjust some of the properties of the controls that you added.

4. Write event-handler procedures for the controls.

These procedures, which are located in the code window for the UserForm, are executed when various events (such as a button click) occur.

5. Write a procedure that will display the UserForm.

This procedure will be located in a VBA module (not in the code module for the UserForm).

6. Add a way to make it easy for the user to execute the procedure you created in Step 5.

You can add a button to a worksheet, a Ribbon command, and so on.

Inserting a New UserForm

To insert a new UserForm, activate the VBE (press Alt+F11), select your workbook's project from the Project window, and then choose Insert⇒UserForm. UserForms have default names like UserForm1, UserForm2, and so on.

You can change the name of a UserForm to make it easier to identify. Select the form and use the Properties window to change the Name property. (Press F4 if the Properties window isn't displayed.) Figure 13-1 shows the Properties window when an empty UserForm is selected.

A workbook can have any number of UserForms, and each UserForm holds a single custom dialog box.

FIGURE 13-1: The Properties window for an empty UserForm.

Adding Controls to a UserForm

To add controls to a UserForm, use the Toolbox. (The VBE doesn't have menu commands that add controls.) If the Toolbox isn't displayed, choose View⇒Toolbox. Figure 13-2 shows the Toolbox. The Toolbox is a floating window, so you can move it to a convenient location.

FIGURE 13-2: Use the Toolbox to add controls to a UserForm.

Click the Toolbox button that corresponds to the control that you want to add and then click inside the dialog box to create the control (using its default size). Or you can click the control and then drag in the dialog box to specify the dimensions for the control.

When you add a new control, it's assigned a name that combines the control type with the numeric sequence for that type of control. For example, if you add a CommandButton control to an empty UserForm, it's named CommandButton1. If you then add a second CommandButton control, it's named CommandButton2.

Renaming all the controls that you'll be manipulating with your VBA code is a good idea. Doing so lets you refer to meaningful names (such as ProductListBox) rather than generic names (such as ListBox1). To change the name of a control, use the Properties window in the VBE. Just select the object and change the Name property.

Toolbox Controls

In the sections that follow, I briefly describe the controls available to you in the Toolbox.

Figure 13-3 shows a UserForm with one of each control. This workbook, named all userform controls.xlsm, is available on the companion CD-ROM.

FIGURE 13-3: This UserForm has one of each of the 15 controls.

Your UserForms can also use other ActiveX controls. See “Customizing the Toolbox,” later in this chapter.

CheckBox

A CheckBox control is useful for getting a binary choice: yes or no, true or false, on or off, and so on. When a CheckBox is checked, it has a value of True; when it's not checked, the CheckBox value is False.

ComboBox

A ComboBox control presents a list of items in a drop-down box and displays only one item at a time. Unlike a ListBox control, you can set up a ComboBox to allow the user to enter a value that doesn't appear in the list of items.

CommandButton

Every dialog box that you create will probably have at least one CommandButton control. Usually, your UserForms will have one CommandButton labeled OK and another labeled Cancel.

Frame

A Frame control is used to enclose other controls. You enclose controls either for aesthetic purposes or to logically group a set of controls. A frame is particularly useful when the dialog box contains more than one set of OptionButton controls.

Image

You can use an Image control to display a graphic image, which can come from a file or can be pasted from the Clipboard. You may want to use an Image control to display your company's logo in a dialog box. The graphics image is stored in the workbook. That way, if you distribute your workbook to someone else, you don't have to include a copy of the graphics file.

Some graphics files are very large, and using such images can make your workbook increase dramatically in size. For best results, use graphics sparingly or use small graphics files.

Label

A Label control simply displays text in your dialog box.

ListBox

The ListBox control presents a list of items, and the user can select an item (or multiple items). ListBox controls are very flexible. For example, you can specify a worksheet range that holds the ListBox items, and this range can consist of multiple columns. Or you can fill the ListBox with items by using VBA.

MultiPage

A MultiPage control lets you create tabbed dialog boxes, like the Format Cells dialog box. By default, a MultiPage control has two pages, but you can add any number of additional pages.

OptionButton

OptionButton controls are useful when the user needs to select one item from a small number of choices. OptionButtons are always used in groups of at least two. When one OptionButton is selected, the other OptionButtons in its group are deselected.

If your UserForm contains more than one set of OptionButtons, the OptionButtons in each set must share a unique GroupName property value. Otherwise, all OptionButtons become part of the same set. Alternatively, you can enclose the OptionButtons in a Frame control, which automatically groups the OptionButtons contained in the frame.

RefEdit

The RefEdit control is used when you need to let the user select a range in a worksheet. This control accepts a typed range address or a range address generated by pointing to the range in a worksheet.

ScrollBar

The ScrollBar control is similar to a SpinButton control. The difference is that the user can drag the ScrollBar button to change the control's value in larger increments. The ScrollBar control is most useful for selecting a value that extends across a wide range of possible values.

SpinButton

The SpinButton control lets the user select a value by clicking either of two arrows: one to increase the value and the other to decrease the value. A SpinButton is often used in conjunction with a TextBox control or Label control, which displays the current value of the SpinButton. A SpinButton can be oriented horizontally or vertically.

TabStrip

A TabStrip control is similar to a MultiPage control, but it's not as easy to use. A TabStrip control, unlike a MultiPage control, doesn't serve as a container for other objects. Generally, you'll find that the MultiPage control is much more versatile.

TextBox

A TextBox control lets the user type text or a value.

ToggleButton

A ToggleButton control has two states: on and off. Clicking the button toggles between these two states, and the button changes its appearance. Its value is either True (pressed) or False (not pressed). I never use this control because I think a CheckBox is much clearer.

Adjusting UserForm Controls

After you place a control in a UserForm, you can move and resize the control by using standard mouse techniques.

You can select multiple controls by Shift-clicking or by clicking and dragging to lasso a group of controls.

A UserForm can contain vertical and horizontal gridlines (displayed as dots) that help you align the controls that you add. When you add or move a control, it snaps to the grid to help you line up the controls. If you don't like to see these gridlines, you can turn them off by choosing Tools⇒Options in the VBE. In the Options dialog box, select the General tab and set your desired options in the Form Grid Settings section.

The Format menu in the VBE window provides several commands to help you precisely align and space the controls in a dialog box. Before you use these commands, select the controls that you want to work with. These commands work just as you'd expect, so I don't explain them here. Figure 13-4 shows a dialog box with several OptionButton controls about to be aligned. Figure 13-5 shows the controls after being aligned and assigned equal vertical spacing.

When you select multiple controls, the last control that you select appears with white handles rather than the normal black handles. The control with the white handles is used as the basis for sizing or positioning.

Adjusting a Control's Properties

Every control has a number of properties that determine how the control looks and behaves. You can change a control's properties, as follows:

• At design time when you're developing the UserForm. You use the Properties window to make design time changes.

• During runtime when the UserForm is being displayed for the user. You use VBA instructions to change a control's properties at runtime.

FIGURE 13-4: Use the Format⇒Align command to change the alignment of controls.

FIGURE 13-5: The OptionButton controls, aligned and evenly spaced.

Using the Properties window

In the VBE, the Properties window adjusts to display the properties of the selected item (which can be a control or the UserForm itself). In addition, you can select a control from the drop-down list at the top of the Properties window. Figure 13-6 shows the Properties window for an OptionButton control.

FIGURE 13-6: The Properties window for an OptionButton control.

The Properties window has two tabs. The Alphabetic tab displays the properties for the selected object in alphabetical order. The Categorized tab displays them grouped into logical categories. Both tabs contain the same properties but in a different order.

To change a property, just click it and specify the new property. Some properties can take on a finite number of values, selectable from a list. If so, the Properties window will display a button with a downward-pointing arrow when that property is selected. Click the button, and you'll be able to select the property's value from the list. For example, the TextAlign property can have any of the following values: 1 - fmTextAlignLeft, 2 - fmTextAlignCenter, or 3 - fmTextAlignRight.

A few properties (for example, Font and Picture) display a small button with an ellipsis when selected. Click the button to display a dialog box associated with the property.

The Image control Picture property is worth mentioning because you can either select a graphic file that contains the image or paste an image from the Clipboard. When pasting an image, first copy it to the Clipboard; then select the Picture property for the Image control, and press Ctrl+V to paste the Clipboard contents.

If you select two or more controls at once, the Properties window displays only the properties that are common to the selected controls.

The UserForm itself has many properties that you can adjust. Some of these properties are then used as defaults for controls that you add to the UserForm. For example, if you change the UserForm Font property, all controls added to the UserForm will use that font. Note, however, that controls already on the UserForm aren't affected.

Common properties

Although each control has its own unique set of properties, many controls have some common properties. For example, every control has a Name property and properties that determine its size and position (Height, Width, Left, and Right).

If you're going to manipulate a control by using VBA, it's an excellent idea to provide a meaningful name for the control. For example, the first OptionButton that you add to a UserForm has a default name of OptionButton1. You refer to this object in your code with a statement such as the following:

OptionButton1.Value = True

But if you give the OptionButton a more meaningful name (such as obLandscape), you can use a statement such as this one:

obLandscape.Value = True

Many people find it helpful to use a name that also identifies the type of object. In the preceding example, I use ob as the prefix to identify the control as an OptionButton. I'm not aware of any standard prefixes, so feel free to invent your own.

You can adjust the properties of several controls at once. For example, you might have several OptionButtons that you want left-aligned. You can simply select all the OptionButtons and then change the Left property in the Properties box. All the selected controls will then take on that new Left property value.

The best way to learn about the various properties for a control is to use the Help system. Simply click on a property in the Property window and press F1. Figure 13-7 shows an example of the type of help provided for a property.

FIGURE 13-7: The Help system provides information about each property for every control.

Accommodating keyboard users

Many users prefer to navigate through a dialog box by using the keyboard: The Tab and Shift+Tab keystrokes cycle through the controls, and pressing a hot key (an underlined letter) operates the control. To make sure that your dialog box works properly for keyboard users, you must be mindful of two issues: tab order and accelerator keys.

Changing the tab order of controls

The tab order determines the sequence in which the controls are activated when the user presses Tab or Shift+Tab. It also determines which control has the initial focus. If a user is entering text into a TextBox control, for example, the TextBox has the focus. If the user clicks an OptionButton, the OptionButton has the focus. The control that's first in the tab order has the focus when a dialog box is first displayed.

To set the tab order of your controls, choose View⇒Tab Order. You can also right-click the UserForm and choose Tab Order from the shortcut menu. In either case, Excel displays the Tab Order dialog box, as shown in Figure 13-8. The Tab Order dialog box lists all the controls, the sequence of which corresponds to the order in which controls pass the focus between each other in the UserForm. To move a control, select it and click the arrow keys up or down. You can choose more than one control (click while pressing Shift or Ctrl) and move them all at once.

Alternatively, you can set an individual control's position in the tab order via the Properties window. The first control in the tab order has a TabIndex property of 0. Changing the TabIndex property for a control may also affect the TabIndex property of other controls. These adjustments are made automatically to ensure that no control has a TabIndex greater than the number of controls. If you want to remove a control from the tab order, set its TabStop property to False.

Some controls, such as Frame and MultiPage, act as containers for other controls. The controls inside a container have their own tab order. To set the tab order for a group of OptionButtons inside a Frame control, select the Frame control before you choose the View⇒Tab Order command.

FIGURE 13-8: Use the Tab Order dialog box to specify the tab order of the controls.

Setting hot keys

You can assign an accelerator key, or hot key, to most dialog box controls. An accelerator key allows the user to access the control by pressing Alt+ the hot key. Use the Accelerator property in the Properties window for this purpose.

Some controls, such as a TextBox, don't have an Accelerator property because they don't display a caption. You still can allow direct keyboard access to these controls by using a Label control. Assign an accelerator key to the Label and put it before the TextBox in the tab order.

Displaying a UserForm

To display a UserForm from VBA, you create a procedure that uses the Show method of the UserForm object. If your UserForm is named UserForm1, the following procedure displays the dialog box on that form:

Sub ShowForm()

UserForm1.Show

End Sub

This procedure must be located in a standard VBA module and not in the code module for the UserForm.

When the UserForm is displayed, it remains visible on-screen until it's dismissed. Usually, you'll add a CommandButton control to the UserForm that executes a procedure that dismisses the UserForm. The procedure can either unload the UserForm (with the Unload command) or hide the UserForm (with the Hide method of the UserForm object). This concept will become clearer as you work through various examples in this and subsequent chapters.

Displaying a modeless UserForm

By default, UserForms are displayed modally. This means that the UserForm must be dismissed before the user can do anything in the worksheet. You can also display a modeless UserForm. When a modeless UserForm is displayed, the user can continue working in Excel, and the UserForm remains visible. To display a modeless UserForm, use the following syntax:

UserForm1.Show vbModeless

Displaying a UserForm based on a variable

In some cases, you may have several UserForms, and your code makes a decision regarding which of them to display. If the name of the UserForm is stored as a string variable, you can use the Add method to add the UserForm to the UserForms collection and then use the Show method of the UserForms collection. Here's an example that assigns the name of a UserForm to the MyForm variable and then displays the UserForm:

MyForm = “UserForm1”

UserForms.Add(MyForm).Show

Loading a UserForm

VBA also has a Load statement. Loading a UserForm loads it into memory, but it's not visible until you use the Show method. To load a UserForm, use a statement like this:

Load UserForm1

If you have a complex UserForm, you might want to load it into memory before it's needed so that it will appear more quickly when you use the Show method. In the majority of situations, however, you don't need to use the Load statement.

About event-handler procedures

After the UserForm is displayed, the user interacts with it — selecting an item from a ListBox, clicking a CommandButton, and so on. In official terminology, the user causes an event to occur. For example, clicking a CommandButton causes the Click event for the CommandButton. You need to write procedures that execute when these events occur. These procedures are sometimes known as event-handler procedures.

Event-handler procedures must be located in the Code window for the UserForm. However, your event-handler procedure can call another procedure that's located in a standard VBA module.

Your VBA code can change the properties of the controls while the UserForm is displayed (that is, at runtime). For example, you could assign to a ListBox control a procedure that changes the text in a Label when an item is selected. This type of manipulation will become clearer later in this chapter.

Closing a UserForm

To close a UserForm, use the Unload command, as shown in this example:

Unload UserForm1

Or, if the code is located in the code module for the UserForm, you can use the following:

Unload Me

In this case, the keyword Me refers to the UserForm. Using Me rather than the UserForm's name eliminates the need to modify your code if you change the name of the UserForm.

Normally, your VBA code should include the Unload command after the UserForm has performed its actions. For example, your UserForm may have a CommandButton that serves as an OK button. Clicking this button executes a macro. One of the statements in the macro will unload the UserForm. The UserForm remains visible on the screen until the macro that contains the Unload statement finishes.

When a UserForm is unloaded, its controls are reset to their original values. In other words, your code won't be able to access the user's choices after the UserForm is unloaded. If the user's choice must be used later on (after the UserForm is unloaded), you need to store the value in a Public variable, declared in a standard VBA module. Or you could store the value in a worksheet cell, or even in the Windows registry.

A UserForm is automatically unloaded when the user clicks the Close button (the X in the UserForm title bar). This action also triggers a UserForm QueryClose event, followed by a UserForm Terminate event.

UserForms also have a Hide method. When you invoke this method, the UserForm disappears, but it remains loaded in memory, so your code can still access the various properties of the controls. Here's an example of a statement that hides a UserForm:

UserForm1.Hide

Or, if the code is in the code module for the UserForm, you can use the following:

Me.Hide

If for some reason you'd like your UserForm to disappear immediately while its macro is executing, use the Hide method at the top of the procedure. For example, in the following procedure, the UserForm disappears immediately when CommandButton1 is clicked. The last statement in the procedure unloads the UserForm.

Private Sub CommandButton1_Click()

Me.Hide

Application.ScreenUpdating = True

For r = 1 To 10000

Cells(r, 1) = r

Next r

Unload Me

End Sub

In this example, I set ScreenUpdating to True to force Excel to hide the UserForm completely. Without that statement, the UserForm may actually remain visible.

In Chapter 15, I describe how to display a progress indicator, which takes advantage of the fact that a UserForm remains visible while the macro executes.

Creating a UserForm: An Example

If you've never created a UserForm, you might want to walk through the example in this section. The example includes step-by-step instructions for creating a simple dialog box and developing a VBA procedure to support the dialog box.

This example uses a UserForm to obtain two pieces of information: a person's name and sex. The dialog box uses a TextBox control to get the name and three OptionButtons to get the sex (Male, Female, or Unknown). The information collected in the dialog box is then sent to the next blank row in a worksheet.

Creating the UserForm

Figure 13-9 shows the completed UserForm for this example. For best results, start with a new workbook with only one worksheet in it. Then follow these steps:

FIGURE 13-9: This dialog box asks the user to enter a name and a sex.

1. Press Alt+F11 to activate the VBE.

2. In the Project window, select the workbook's project and choose Insert⇒UserForm to add an empty UserForm.

The UserForm's Caption property will have its default value: UserForm1.

3. Use the Properties window to change the UserForm's Caption property to Get Name and Sex.

(If the Properties window isn't visible, press F4.)

4. Add a Label control and adjust the properties as follows:

Property	Value
Accelerator	N
Caption	Name:
TabIndex	0

5. Add a TextBox control and adjust the properties as follows:

Property	Value
Name	TextName
TabIndex	1

6. Add a Frame control and adjust the properties as follows:

Property	Value
Caption	Sex
TabIndex	2

7. Add an OptionButton control inside the frame and adjust the properties as follows:

Property	Value
Accelerator	M
Caption	Male
Name	OptionMale
TabIndex	0

8. Add another OptionButton control inside the frame and adjust the properties as follows:

Property	Value
Accelerator	F
Caption	Female
Name	OptionFemale
TabIndex	1

9. Add yet another OptionButton control inside the Frame and adjust the properties as follows:

Property	Value
Accelerator	U
Caption	Unknown
Name	OptionUnknown
TabIndex	2
Value	True

10. Add a CommandButton control outside the Frame and adjust the properties as follows:

Property	Value
Caption	OK
Default	True
Name	OKButton
TabIndex	3

11. Add another CommandButton control and adjust the properties as follows:

Property	Value
Caption	Close
Cancel	True
Name	CloseButton
TabIndex	4

When you're creating several controls that are similar, you may find it easier to copy an existing control rather than create a new one. To copy a control, press Ctrl while you drag the control to make a new copy of it. Then adjust the properties on the copied control.

Writing code to display the dialog box

Next, you add an ActiveX CommandButton to the worksheet. This button will execute a procedure that displays the UserForm. Here's how:

1. Activate Excel.

(Alt+F11 is the shortcut key combination.)

2. Choose Developer⇒Controls⇒Insert and click CommandButton from the ActiveX Controls section.

3. Drag in the worksheet to create the button.

If you like, you can change the caption for the worksheet CommandButton. To do so, right-click the button and choose CommandButton Object⇒Edit from the shortcut menu. You can then edit the text that appears on the CommandButton. To change other properties of the object, right-click and choose Properties. Then make the changes in the Properties box.

4. Double-click the CommandButton.

This step activates the VBE. More specifically, the code module for the worksheet will be displayed, with an empty event-handler procedure for the worksheet's CommandButton.

5. Enter a single statement in the CommandButton1_Click procedure (see Figure 13-10).

This short procedure uses the Show method of an object (UserForm1) to display the UserForm.

FIGURE 13-10: The CommandButton1_Click procedure is executed when the button on the worksheet is clicked.

Testing the dialog box

The next step is to re-activate Excel and try out the procedure that displays the dialog box.

When you click the CommandButton on the worksheet, you'll find that nothing happens. Rather, the button is selected. That's because Excel is still in design mode — which happens automatically when you insert an ActiveX control. To exit design mode, click the Developer⇒Controls⇒Design Mode button. To make any changes to your CommandButton, you'll need to put Excel back into design mode.

When you exit design mode, clicking the button will display the UserForm (see Figure 13-11).

When the dialog box is displayed, enter some text into the text box and click OK. Nothing happens — which is understandable because you haven't yet created an event-handler procedure for the OK button.

Click the X (Close) button in the UserForm title bar to dismiss the dialog box.

FIGURE 13-11: The CommandButton's Click event procedure displays the UserForm.

Adding event-handler procedures

In this section, I explain how to write the procedures that will handle the events that occur when the UserForm is displayed. To continue the example, do the following:

1. Press Alt+F11 to activate the VBE.

2. Make sure that the UserForm is displayed and double-click the CommandButton captioned Close.

This step activates the Code window for the UserForm and inserts an empty procedure named CloseButton_Click. Notice that this procedure consists of the object's name, an underscore character, and the event that it handles.

3. Modify the procedure as follows.

(This is the event handler for the CloseButton's Click event.)

Private Sub CloseButton_Click()

Unload UserForm1

End Sub

This procedure, which is executed when the user clicks the Close button, simply unloads the UserForm.

4. Press Shift+F7 to redisplay UserForm1 (or click the View Object icon at the top of the Project Explorer window).

5. Double-click the OK button and enter the following procedure.

(This is the event handler for the OKButton's Click event.)

Private Sub OKButton_Click()

Dim NextRow As Long

‘ Make sure Sheet1 is active

Sheets(“Sheet1”).Activate

‘ Determine the next empty row

NextRow = _

Application.WorksheetFunction.CountA(Range(“A:A”)) + 1

‘ Transfer the name

Cells(NextRow, 1) = TextName.Text

‘ Transfer the sex

If OptionMale Then Cells(NextRow, 2) = “Male”

If OptionFemale Then Cells(NextRow, 2) = “Female”

If OptionUnknown Then Cells(NextRow, 2) = “Unknown”

‘ Clear the controls for the next entry

TextName.Text = “”

OptionUnknown = True

TextName.SetFocus

End Sub

6. Activate Excel and click the CommandButton again to display the UserForm and then re-un the procedure again.

You'll find that the UserForm controls now function correctly. You can use them to add new names to the list in the worksheet.

Here's how the OKButton_Click procedure works: First, the procedure makes sure that the proper worksheet (Sheet1) is active. It then uses Excel's COUNTA function to determine the next blank cell in column A. Next, it transfers the text from the TextBox control to column A. It then uses a series of If statements to determine which OptionButton was selected and writes the appropriate text (Male, Female, or Unknown) to column B. Finally, the dialog box is reset to make it ready for the next entry. Notice that clicking OK doesn't close the dialog box. To end data entry (and unload the UserForm), click the Close button.

Validating the data

Play around with this example some more, and you'll find that it has a small problem: It doesn't ensure that the user actually enters a name into the text box. To make sure that the user enters a name, insert the following code in the OKButton_Click procedure, before the text is transferred to the worksheet. It ensures that the user enters a name (well, at least some text) in the TextBox. If the TextBox is empty, a message appears, and the focus is set to the TextBox so that the user can try again. The Exit Sub statement ends the procedure with no further action.

‘ Make sure a name is entered

If TextName.Text = “” Then

MsgBox “You must enter a name.”

TextName.SetFocus

Exit Sub

End If

The finished dialog box

After making all these modifications, you'll find that the dialog box works flawlessly. (Don't forget to test the hot keys.) In real life, you'd probably need to collect more information than just name and sex. However, the same basic principles apply. You just need to deal with more UserForm controls.

A workbook with this example is available on the companion CD-ROM in a file named get name and sex.xlsm.

Understanding UserForm Events

Each UserForm control (as well as the UserForm itself) is designed to respond to certain types of events, and a user or Excel can trigger these events. For example, clicking a CommandButton generates a Click event for the CommandButton. You can write code that is executed when a particular event occurs.

Some actions generate multiple events. For example, clicking the upward arrow of a SpinButton control generates a SpinUp event and also a Change event. When a UserForm is displayed by using the Show method, Excel generates an Initialize event and an Activate event for the UserForm. (Actually, the Initialize event occurs when the UserForm is loaded into memory and before it's actually displayed.)

Excel also supports events associated with a Sheet object, Chart objects, and the ThisWorkbook object. I discuss these types of events in Chapter 18.

Learning about events

To find out which events are supported by a particular control, do the following:

1. Add a control to a UserForm.

2. Double-click the control to activate the code module for the UserForm.

The VBE will insert an empty event-handler procedure for the default event for the control.

3. Click the drop-down list in the upper-right corner of the module window, and you'll see a complete list of events for the control.

Figure 13-12 shows the list of events for a CheckBox control.

FIGURE 13-12: The event list for a CheckBox control.

4. Select an event from the list, and the VBE will create an empty event-handler procedure for you.

To find out specific details about an event, consult the Help system. The Help system also lists the events available for each control. When you locate an event for an object, make sure that the Help system table of contents is displayed. Then you can see a list of all other events for the object.

Event-handler procedures incorporate the name of the object in the procedure's name. Therefore, if you change the name of a control, you'll also need to make the appropriate changes to the control's event-handler procedure(s). The name changes aren't performed automatically! To make things easy on yourself, it's a good idea to provide names for your controls before you begin creating event-handler procedures.

UserForm events

Several events are associated with showing and unloading a UserForm:

• Initialize: Occurs before a UserForm is loaded or shown but doesn't occur if the UserForm was previously hidden.

• Activate: Occurs when a UserForm is shown.

• Deactivate: Occurs when a UserForm is deactivated but doesn't occur if the form is hidden.

• QueryClose: Occurs before a UserForm is unloaded.

• Terminate: Occurs after the UserForm is unloaded.

Often, it‘s critical that you choose the appropriate event for your event-handler procedure and that you understand the order in which the events occur. Using the Show method invokes the Initialize and Activate events (in that order). Using the Load command invokes only the Initialize event. Using the Unload command triggers the QueryClose and Terminate events (in that order). Using the Hide method doesn't trigger either of these events.

The companion CD-ROM contains a workbook (named userform events.xlsm) that monitors all these events and displays a message box when an event occurs. If you're confused about UserForm events, studying the code in this example should clear things up.

SpinButton events

To help clarify the concept of events, this section takes a close look at the events associated with a SpinButton control. Some of these events are associated with other controls, and some are unique to the SpinButton control.

The companion CD-ROM contains a workbook that demonstrates the sequence of events that occur for a SpinButton and the UserForm that contains it. The workbook, named spinbutton events.xlsm, contains a series of event-handler routines — one for each SpinButton and UserForm event. Each of these routines simply displays a message box that tells you the event that just fired.

Table 13-1 lists all the events for the SpinButton control.

Table 13-1: SpinButton Events

Event	Description
AfterUpdate	Occurs after the control is changed through the user interface.
BeforeDragOver	Occurs when a drag-and-drop operation is in progress.
BeforeDropOrPaste	Occurs when the user is about to drop or paste data onto the control.
BeforeUpdate	Occurs before the control is changed.
Change	Occurs when the Value property changes.
Enter	Occurs before the control actually receives the focus from a control on the same UserForm.
Error	Occurs when the control detects an error and can't return the error information to a calling program.
Exit	Occurs immediately before a control loses the focus to another control on the same form.
KeyDown	Occurs when the user presses a key and the object has the focus.
KeyPress	Occurs when the user presses any key that produces a typeable character.
KeyUp	Occurs when the user releases a key and the object has the focus.
SpinDown	Occurs when the user clicks the lower (or left) SpinButton arrow.
SpinUp	Occurs when the user clicks the upper (or right) SpinButton arrow.

A user can operate a SpinButton control by clicking it with the mouse or (if the control has the focus) by using the up-arrow and down-arrow keys.

Mouse-initiated events

When the user clicks the upper SpinButton arrow, the following events occur in this precise order:

1. Enter (triggered only if the SpinButton did not already have the focus)

2. Change

3. SpinUp

Keyboard-initiated events

The user can also press Tab to set the focus to the SpinButton and then use the arrow keys to increment or decrement the control. If so, the following events occur (in this order):

1. Enter

2. KeyDown

3. Change

4. SpinUp (or SpinDown)

5. KeyUp

What about changes via code?

The SpinButton control can also be changed by VBA code — which also triggers the appropriate event(s). For example, the following statement sets the SpinButton1 Value property to 0 and also triggers the Change event for the SpinButton control — but only if the SpinButton value was not already 0:

SpinButton1.Value = 0

You might think that you could disable events by setting the EnableEvents property of the Application object to False. Unfortunately, this property applies only to events that involve true Excel objects: Workbooks, Worksheets, and Charts.

Pairing a SpinButton with a TextBox

A SpinButton has a Value property, but this control doesn't have a caption in which to display its value. In many cases, however, you'll want the user to see the SpinButton value. And sometimes you'll want the user to be able to change the SpinButton value directly instead of clicking the SpinButton repeatedly.

The solution is to pair a SpinButton with a TextBox, which enables the user to specify a value either by typing it into the TextBox directly or by clicking the SpinButton to increment or decrement the value in the TextBox.

Figure 13-13 shows a simple example. The SpinButton's Min property is 1, and its Max property is 100. Therefore, clicking the SpinButton's arrows will change its value to an integer between 1 and 100.

FIGURE 13-13: This SpinButton is paired with a TextBox.

This workbook is available on the companion CD-ROM. The file is named spinbutton and textbox.xlsm.

The code required to link a SpinButton with a TextBox is relatively simple. It's basically a matter of writing event-handler procedures to ensure that the SpinButton's Value property is always in sync with the TextBox's Text property.

The following procedure is executed whenever the SpinButton's Change event is triggered. That is, the procedure is executed when the user clicks the SpinButton or changes its value by pressing the up arrow or down arrow.

Private Sub SpinButton1_Change()

TextBox1.Text = SpinButton1.Value

End Sub

The procedure simply assigns the SpinButton's Value to the Text property of the TextBox control. Here, the controls have their default names (SpinButton1 and TextBox1). If the user enters a value directly into the TextBox, its Change event is triggered, and the following procedure is executed:

Private Sub TextBox1_Change()

NewVal = Val(TextBox1.Text)

If NewVal >= SpinButton1.Min And _

NewVal <= SpinButton1.Max Then _

SpinButton1.Value = NewVal

End Sub

This procedure starts by using VBA's Val function to convert the text in the TextBox to a value. (If the TextBox contains non-numeric text, the Val function returns 0.) The next statement determines whether the value is within the proper range for the SpinButton. If so, the SpinButton's Value property is set to the value entered in the TextBox.

The example is set up so that clicking the OK button (which is named OKButton) transfers the SpinButton's value to the active cell. The event handler for this CommandButton's Click event is as follows:

Private Sub OKButton_Click()

‘ Enter the value into the active cell

If CStr(SpinButton1.Value) = TextBox1.Text Then

ActiveCell = SpinButton1.Value

Unload Me

Else

MsgBox “Invalid entry.”, vbCritical

TextBox1.SetFocus

TextBox1.SelStart = 0

TextBox1.SelLength = Len(TextBox1.Text)

End If

End Sub

This procedure does one final check: It makes sure that the text entered in the TextBox matches the SpinButton's value. This check is necessary in the case of an invalid entry. For example, if the user enters 3r into the TextBox, the SpinButton's value would not be changed, and the result placed in the active cell would not be what the user intended. Notice that the SpinButton's Value property is converted to a string by using the CStr function. This conversion ensures that the comparison won't generate an error if a value is compared with text. If the SpinButton's value doesn't match the TextBox's contents, a message box is displayed. Notice that the focus is set to the TextBox object, and the contents are selected (by using the SelStart and SelLength properties). This setup makes it very easy for the user to correct the entry.

Referencing UserForm Controls

When working with controls on a UserForm, the VBA code is usually contained in the code window for the UserForm. You can also refer to UserForm controls from a general VBA module. To do so, you need to qualify the reference to the control by specifying the UserForm name. For example, consider the following procedure, which is located in a VBA module. It simply displays the UserForm named UserForm1.

Sub GetData()

UserForm1.Show

End Sub

Assume that UserForm1 contains a text box (named TextBox1), and you want to provide a default value for the text box. You could modify the procedure as follows:

Sub GetData()

UserForm1.TextBox1.Value = “John Doe”

UserForm1.Show

End Sub

Another way to set the default value is to take advantage of the UserForm's Initialize event. You can write code in the UserForm_Initialize procedure, which is located in the code module for the UserForm. Here's an example:

Private Sub UserForm_Initialize()

TextBox1.Value = “John Doe”

End Sub

Notice that when the control is referenced in the code module for the UserForm, you don't need to qualify the references with the UserForm name. However, qualifying references to controls does have an advantage: You'll then be able to take advantage of the Auto List Members feature, which lets you choose the control names from a drop-down list.

Rather than use the actual name of the UserForm, it's preferable to use Me. Then, if you change the name of the UserForm, you won't need to replace the references in your code.

Customizing the Toolbox

When a UserForm is active in the VBE, the Toolbox displays the controls that you can add to the UserForm. If the Toolbox isn't visible, choose View⇒Toolbox to display it. This section describes ways to customize the Toolbox.

Adding new pages to the Toolbox

The Toolbox initially contains a single tab. Right-click this tab and select New Page to add a new tab to the Toolbox. You can also change the text displayed on the tab by selecting Rename from the shortcut menu.

Customizing or combining controls

A very handy feature lets you customize a control and then save it for future use. You can, for example, create a CommandButton control that's set up to serve as an OK button. Set the following properties to customize the CommandButton: Width, Height, Caption, Default, and Name. Then drag the customized CommandButton to the Toolbox to create a new control. Right-click the new control to rename it or change its icon.

You can also create a new Toolbox entry that consists of multiple controls. For example, you can create two CommandButtons that represent a UserForm's OK and Cancel buttons. Customize them as you like and then select them both and drag them to the Toolbox. Then, you can use this new Toolbox control to add two customized buttons in one fell swoop.

This type of customization also works with controls that act as containers. For example, create a Frame control and add four customized OptionButtons, neatly spaced and aligned. Then drag the Frame to the Toolbox to create a customized Frame control.

To help identify customized controls, right-click the control and select Customize xxx from the shortcut menu (where xxx is the control's name). You see a new dialog box that lets you change the ToolTip text, edit the icon, or load a new icon image from a file.

You may want to place your customized controls on a separate page in the Toolbox. This lets you export the entire page so that you can share it with other Excel users. To export a Toolbox page, right-click the tab and select Export Page.

The companion CD-ROM contains a pag file (named newcontrols.pag) that contains some customized controls. You can import this file as a new page in your Toolbox. Right-click a tab and select Import Page. Then locate the pag file. Your Toolbox will resemble Figure 13-14. The new controls include an exclamation point image, a “critical” red x image, a question mark image, an information image, OK and Cancel buttons, a Frame with four OptionButton controls, a TextBox, a Spinner, and six CheckBox controls.

FIGURE 13-14: The Toolbox, with a new page of controls.

Adding new ActiveX controls

UserForms can use other ActiveX controls developed by Microsoft or other vendors. To add an additional ActiveX control to the Toolbox, right-click the Toolbox and select Additional Controls. You see the dialog box shown in Figure 13-15.

The Additional Controls dialog box lists all ActiveX controls that are installed on your system. Select the control(s) that you want to add and then click OK to add an icon for each selected control.

FIGURE 13-15: The Additional Controls dialog box lets you add other ActiveX controls.

Not all ActiveX controls that are installed on your system will work in Excel UserForms. In fact, most of them probably won't work. Also, some controls require a license in order to use them in an application. If you (or the users of your application) aren't licensed to use a particular control, an error will occur.

Creating UserForm Templates

You may find that when you design a new UserForm, you tend to add the same controls each time. For example, every UserForm might have two CommandButtons that serve as OK and Cancel buttons. In the previous section, I describe how to create a new control that combines these two (customized) buttons into a single control. Another option is to create your UserForm template and then export it so that you can import it into other projects. An advantage is that the event-handler code for the controls is stored with the template.

Start by creating a UserForm that contains all the controls and customizations that you'd need to reuse in other projects. Then make sure that the UserForm is selected and choose File⇒Export File (or press Ctrl+E). You'll be prompted for a filename.

Then, when you start your next project, choose File⇒Import File to load the saved UserForm.

A UserForm Checklist

Before you unleash a UserForm on end users, be sure that everything is working correctly. The following checklist should help you identify potential problems:

• Are similar controls the same size?

• Are the controls evenly spaced?

• Is the dialog box too overwhelming? If so, you may want to group the controls by using a MultiPage control.

• Can every control be accessed with a hot key?

• Are any of the hot keys duplicated?

• Is the tab order set correctly?

• Will your VBA code take appropriate action if the user presses Esc or clicks the Close button on the UserForm?

• Are there any misspellings in the text?

• Does the dialog box have an appropriate caption?

• Will the dialog box display properly at all video resolutions?

• Are the controls grouped logically (by function)?

• Do ScrollBar and SpinButton controls allow valid values only?

• Does the UserForm use any controls that might not be installed on every system?

• Are ListBoxes set properly (Single, Multi, or Extended)? See Chapter 14 for details on ListBox controls.