In This Chapter
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. A custom dialog box is created on a UserForm, and you access UserForms in Visual Basic Editor (VBE).
Following is the typical sequence that you’ll follow when you create a UserForm:
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.
Write a procedure that will display the UserForm.
This procedure will be located in a standard VBA module (not in the code module for the UserForm).
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, create a shortcut menu command, and so on.
To insert a new UserForm, activate VBE (press Alt+F11), select your workbook’s project from the Project window, and then choose Insert ➜ UserForm. UserForms have default names: UserForm1, UserForm2, and so on.
A workbook can have any number of UserForms, and each UserForm holds a single custom dialog box.
To add controls to a UserForm, use the Toolbox, as shown in Figure 13.2. (VBE doesn’t have menu commands that add controls.) If the Toolbox isn’t displayed, choose View ➜ Toolbox. The Toolbox is a floating window, so you can move it to a convenient location.
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.
In the sections that follow, I briefly describe the controls available to you in the Toolbox.
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.
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.
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.
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.
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.
A Label control simply displays text in your dialog box.
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.
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 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.
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.
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.
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.
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.
A TextBox control lets the user type text or a value.
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.
After you place a control in a UserForm, you can move and resize the control by using standard mouse techniques.
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 VBE. In the Options dialog box, select the General tab and set your desired options in the Form Grid Settings section. These gridlines are for design only and do not appear when the dialog box is displayed to the user.
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 with which you want to work. 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 they are aligned and assigned equal vertical spacing.
Every control has a number of properties that determine how the control looks and behaves. You can change a control’s properties, as follows:
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.
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.
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.
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.
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 and the hot key. Use the Accelerator property in the Properties window for this purpose.
To display a UserForm from VBA, you create a procedure that uses the Userform.Show method. 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.
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
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
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
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.
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.
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 control that functions as an OK button. Clicking this button executes a macro, and 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.
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.
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.
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:
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.
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.)
Add a Label control and adjust the properties as follows:
Property | Value |
Name | lblName |
Accelerator | N |
Caption | Name: |
TabIndex | 0 |
Add a TextBox control and adjust the properties as follows:
Property | Value |
Name | tbxName |
TabIndex | 1 |
Add a Frame control and adjust the properties as follows:
Property | Value |
Name | frmSex |
Caption | Sex |
TabIndex | 2 |
Add an OptionButton control inside the frame and adjust the properties as follows:
Property | Value |
Accelerator | M |
Caption | Male |
Name | OptMale |
TabIndex | 0 |
Add another OptionButton control inside the frame and adjust the properties as follows:
Property | Value |
Accelerator | F |
Caption | Female |
Name | OptFemale |
TabIndex | 1 |
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 |
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 |
Add another CommandButton control and adjust the properties as follows:
Property | Value |
Accelerator | C |
Caption | Close |
Cancel | True |
Name | cmdClose |
TabIndex | 4 |
Next, you add an ActiveX CommandButton to the worksheet. This button will execute a procedure that displays the UserForm. Here’s how:
Activate Excel.
(Alt+F11 is the shortcut key combination.)
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.
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.
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.
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.
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:
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.
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.
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
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.
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.
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 up 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.)
To find out which events are supported by a particular control, do the following:
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.
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.
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.
A UserForm has quite a few events. Here are the events associated with showing and unloading a UserForm:
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.
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 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 arrow keys.
When the user clicks the upper SpinButton arrow, the following events occur in this order:
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):
The SpinButton control can also be changed by VBA code — which also triggers the appropriate event(s). For example, the following statement sets the spbDemo Value property to 0 and also triggers the Change event for the SpinButton control — but only if the SpinButton value was not already 0:
Me.spbDemo.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.
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.
When working with controls on a UserForm, the event-handler VBA code is usually contained in the code window for the UserForm. In such a case, you do not need to qualify references to the controls because the controls are assumed to belong to 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() Me.TextBox1.Value ="John Doe" End Sub
Note that when the control is referenced in the code module for the UserForm, you can use the Me keyword instead of the UserForm name. In fact, when you’re in the Userform’s code module, you aren’t required to use the Me keyword. If you omit it, VBA assumes you’re referencing the control on the form you’re in. However, qualifying references to controls does have an advantage: It allows you to take advantage of the Auto List Members feature, which lets you choose the control names from a drop-down list.
When a UserForm is active in 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.
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.
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:
The four icons are the same images displayed by the MsgBox function.
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.
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 preceding 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.
Before you unleash a UserForm on end users, be sure that everything is working correctly. The following checklist should help you identify potential problems:
3.145.69.192