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.

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. Excel’s built-in Format Cells dialog box uses a MultiPage control. 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 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.

Using the Properties window

In 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.

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, and then select the Picture property for the Image control and press Ctrl+V to paste the Clipboard contents.

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, you’ll probably want 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:

Me.OptionButton1.Value = True

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

Me.optLandscape.Value = True

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 a property in the Property window and press F1.

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 in 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 or right-click the UserForm and choose Tab Order from the shortcut menu. In either case, Excel displays the Tab Order dialog box, which 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 press the arrow keys up or down or click the Move Up or Move Down buttons. You can choose more than one control (by Shift- or Ctrl-clicking) and move them all at once.

Alternatively, you can set an individual control’s position in the tab order by using 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.

Adjusting the display position

The StartUpPosition property of the UserForm object determines where on the screen the dialog box will be displayed. You can specify this property in the Properties box or at runtime. The default value is 1 – CenterOwner, which displays the dialog box in the center of the Excel window.

If you use a dual-monitor system, however, you’ll find that sometimes the StartUpPosition property seems to be ignored. Specifically, if the Excel window is on the secondary monitor, the UserForm may appear on the left edge of the primary window.

The following code ensures that the UserForm is always displayed in the center of the Excel window:

With UserForm1
  .StartUpPosition = 0
  .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
  .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
  .Show
End With

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 and triggers the UserForm’s Initialize event. But the dialog box is 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 that takes a bit of time to initialize, 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 triggers an event. For example, clicking a CommandButton triggers the Click event for the CommandButton control. You need to write procedures that execute when these events occur. These procedures are sometimes known as event-handler procedures.

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 is the key to making dialog boxes interactive, and will become clearer later in this chapter.

Creating the UserForm

Figure 13.8 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:

1. Press Alt+F11 to activate 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
   Name	lblName
   Accelerator	N
   Caption	Name:
   TabIndex	0

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

   Property	Value
   Name	tbxName
   TabIndex	1

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

   Property	Value
   Name	frmSex
   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	OptMale
   TabIndex	0

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

   Property	Value
   Accelerator	F
   Caption	Female
   Name	OptFemale
   TabIndex	1

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

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

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

    Property	Value
    Accelerator	O
    Caption	OK
    Default	True
    Name	cmdOK
    TabIndex	3

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

    Property	Value
    Accelerator	C
    Caption	Close
    Cancel	True
    Name	cmdClose
    TabIndex	4

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 (the bottom group of controls).

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 VBE. More specifically, the code module for the worksheet will be displayed, with an empty event-handler procedure for the worksheet’s CommandButton control.

5. Enter a single statement in the CommandButton1_Click procedure (see Figure 13.9).

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

Testing the dialog box

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

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

When the dialog box is displayed, enter some text in 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.

Adding event-handler procedures

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

1. Press Alt+F11 to activate VBE.

2. Make sure 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 cmdClose_Click. Note 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 cmdClose_Click()
       Unload Me
   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 cmdOK button’s Click event.)

   Private Sub cmdOK_Click()
       Dim lNextRow As Long
       Dim wf As WorksheetFunction
       
       Set wf = Application.WorksheetFunction
       
   '   Make sure a name is entered
       If Len(Me.tbxName.Text) = 0 Then
           MsgBox"You must enter a name."
           Me.tbxName.SetFocus
       Else
       '   Determine the next empty row
           lNextRow = wf.CountA(Sheet1.Range("A:A")) + 1
       '   Transfer the name
           Sheet1.Cells(lNextRow, 1) = Me.tbxName.Text
           
       '   Transfer the sex
           With Sheet1.Cells(lNextRow, 2)
               If Me.optMale.Value Then .Value ="Male"
               If Me.optFemale.Value Then .Value ="Female"
               If Me.optUnknown.Value Then .Value ="Unknown"
           End With
           
       '   Clear the controls for the next entry
           Me.tbxName.Text = vbNullString
           Me.optUnknown.Value = True
           Me.tbxName.SetFocus
       End If
   End Sub
   

6. Activate Excel and click the CommandButton again to display the UserForm and then run the procedure again.

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

Here’s how the cmdOK_Click procedure works: First, the procedure makes sure that something was entered in the TextBox. If nothing is entered (the length of the text is 0), it dispalys a message and sets the focus back to the TextBox. If something was entered, it uses the Excel 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. Note that clicking OK doesn’t close the dialog box. To end data entry (and unload the UserForm), click the Close button.

The finished dialog box

After you’ve entered the two event-handler procedures, 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 the name and sex. The same basic principles apply; you would just need to deal with more UserForm controls.

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.

   VBE inserts 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.

You see a complete list of events for the control. Figure 13.11 shows the list of events for a CheckBox control.

4. Select an event from the list.

   VBE creates 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.

UserForm events

A UserForm has quite a few events. Here are the events associated with showing and unloading a UserForm:

• Initialize: Occurs when a UserForm is loaded but before it’s shown and 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

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.

Table 13.1 lists all the events for the SpinButton control.

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

Me.spbDemo.Value = 0

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 in the TextBox directly or by clicking the SpinButton to increment or decrement the value in the TextBox.

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

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. In the following code, the controls have their default names (SpinButton1 and TextBox1).

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 an arrow key.

Private Sub SpinButton1_Change()
    Me.TextBox1.Text = Me.SpinButton1.Value
End Sub

The procedure assigns the SpinButton’s Value to the Text property of the TextBox control. If the user enters a value directly in the TextBox, its Change event is triggered, and the following procedure is executed:

Private Sub TextBox1_Change()
    Dim NewVal As Long
    If IsNumeric(Me.TextBox1.Text) Then
        NewVal = Val(Me.TextBox1.Text)
        If NewVal >= Me.SpinButton1.Min And _
            NewVal <= Me.SpinButton1.Max Then _
            Me.SpinButton1.Value = NewVal
    End If
End Sub

This procedure starts by determining whether the entry in the TextBox is a number. If so, the procedure continues and the text is assigned to the NewVal variable. 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. If the entry is not numeric or is out of range, nothing happens.

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(Me.SpinButton1.Value) = Me.TextBox1.Text Then
        ActiveCell.Value = Me.SpinButton1.Value
        Unload Me
    Else
        MsgBox"Invalid entry.", vbCritical
        Me.TextBox1.SetFocus
        Me.TextBox1.SelStart = 0
        Me.TextBox1.SelLength = Len(Me.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 in 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. Note 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 easy for the user to correct the entry.

Adding new pages to the Toolbox

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

Customizing or combining controls

A 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 choose 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.

Figure 13.13 shows a new page with eight customized controls:

• A Frame with four OptionButtons
• A TextBox and Spinner
• Six Checkboxes
• A “critical” red X icon
• An exclamation point icon
• A question mark icon
• An Information icon
• Two CommandButtons

The four icons are the same images displayed by the MsgBox function.

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 choose Additional Controls. You see the dialog box shown in Figure 13.14.

The Additional Controls dialog box lists all ActiveX controls 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.