Userforms enable you to display information and allow the user to input information. InputBox and MsgBox controls are simple ways of doing this. You can use the userform controls in the VB Editor to create more complex forms.
This chapter covers simple user interface using input boxes and message boxes and the basics of creating userforms in the VB Editor. For more advanced programming, see Chapter 23, “Advanced Userform Techniques.”
The InputBox function is used to create a basic interface element that requests input from the user before the program can continue. You can configure the prompt, the title for the window, a default value, the window position, and user help files. Only two buttons are provided: OK and Cancel. The returned value is a string.
The following code asks the user for the number of months to be averaged. Figure 10.1 shows the resulting InputBox.
AveMos = InputBox(Prompt:="Enter the number " & _ " of months to average", Title:="Enter Months", _ Default:="3")
The MsgBox function creates a message box that displays information and waits for the user to click a button before continuing. Whereas InputBox only has OK and Cancel buttons, MsgBox allows you to choose from several configurations of buttons, including Yes, No, OK, and Cancel. You can also configure the prompt, the window title, and help files. The following code produces a simple prompt to find out whether the user wants to continue. A Select Case statement is then used to continue the program with the appropriate action. Figure 10.2 shows the resulting customized message box.
MyMsg = "Do you want to Continue?"
Response = MsgBox(myMsg, vbExclamation + vbYesNoCancel, myTitle)
Select Case Response
Case Is = vbYes
ActiveWorkbook.Close SaveChanges:=False
Case Is = vbNo
ActiveWorkbook.Close SaveChanges:=True
Case Is = vbCancel
Exit Sub
End Select
Userforms combine the capabilities of InputBox and MsgBox to create a more efficient way of interacting with the user. For example, rather than have the user fill out personal information on a sheet, you can create a userform that prompts for the required data (see Figure 10.3).
Insert a userform in the VB Editor by choosing Insert, UserForm from the main menu. A UserForm module is added to the Project Explorer, a blank form appears in the window where your code usually is, and the Controls toolbox appears.
You can resize the form by grabbing and dragging the handles on the right side, bottom edge, or lower-right corner of the userform. To add controls to the form, click the desired control in the toolbox and draw it on the form. Controls can be moved and resized at any time.
Note
The toolbox, by default, displays the most common controls. To access more controls, right-click the toolbox and select Additional Controls. But be careful; other users may not have the same additional controls as you do. If you send them a form with a control they don’t have installed, the program will generate an error.
After a control is added to a form, its properties can be changed from the Properties window. These properties can be set manually now or set later programmatically. If the Properties window is not visible, you can bring it up by selecting View, Properties Window. Figure 10.4 shows the Properties window for a text box.
A userform can be called from any module. FormName.Show pops up a form for the user:
frm_AddEmp.Show
The Load method can also be used to call a userform. This allows a form to be loaded, but remain hidden:
Load frm_AddEmp
To hide a userform, use the Hide method. The form is still active but is now hidden from the user. The controls on the form can still be accessed programmatically:
Frm_AddEmp.Hide
The Unload method unloads the form from memory and removes it from the user’s view. The form can no longer be accessed by the user or programmatically:
Unload Me
The code for a control goes in the forms module. Unlike the other modules, double-clicking the Forms module opens up the form in Design view. To view the code, right-click the module or the userform in Design mode and select View Code.
Just like a worksheet, a userform has events triggered by actions. After the userform has been added to the project, the events are available in the Properties drop-down list at the top-right of the code window (see Figure 10.5) by selecting UserForm from the Objects drop-down on the left.
The available events for userforms are described in Table 10.1.
Table 10.1. The Events for Userforms
Event | Description |
|---|---|
| Occurs when a userform is shown either from being loaded or unhidden. This event is triggered after the |
| Occurs when a control is added to a userform at runtime. Does not run at design time or upon userform initialization. |
| Occurs while the user does a drag and drop onto the userform. |
| Occurs right before the user is about to drop or paste data into the userform. |
| Occurs when the user clicks the userform with the mouse. |
| Occurs when the user double-clicks the userform with the mouse. |
| Occurs when a userform is deactivated. |
| Occurs when the userform runs into an error and can’t return the error information. |
| Occurs when the userform is first loaded, before the |
| Occurs when the user presses a key on the keyboard. |
| Occurs when the user presses an ANSI key. An ANSI key is a typeable character, such as the letter A. A nontypeable character would be, for example, the Tab key. |
| Occurs when the user releases a key on the keyboard. |
| Occurs when the control changes size. |
| Occurs when the user presses the mouse button within the borders of the userform. |
| Occurs when the user moves the mouse within the borders of the userform. |
| Occurs when the user releases the mouse button within the borders of the userform. |
| Occurs before a userform closes. It allows you to recognize the method used to close a form and have code respond accordingly. |
| Occurs when a control is deleted from within the userform. |
| Occurs when the userform is resized. |
| Occurs when the scrollbar box, if visible, is repositioned. |
| Occurs after the userform has been unloaded. It’s triggered after |
| Occurs when the zoom value is changed. |
To program a control, highlight it and select View, Code. The footer, header, and default action for the control is automatically entered in the programming field. To see what other actions are available for a control, select the control from the Object drop-down and view the actions in the Properties drop-down, as shown in Figure 10.6.
The controls are objects, like ActiveWorkbook. They have properties and methods, dependent on the type of control. Most of the programming for the controls is done behind the form; but if another module needs to refer to a control, the parent, which is the form, needs to be included with the object.
Private Sub btn_EmpCancel_Click() Unload Me End Sub
The preceding code can be broken down into three sections:
btn_EmpCancel—. Name given to the controlClick—. Action of the controlUnload Me—. The code behind the control (in this case, unloading the form)
Each control has different events associated with it, allowing you to code what happens based on the user’s actions. A table reviewing the control events is available at the end of each of the sections that follow.
Our basic form, as shown in Figure 10.7, consists of labels, text boxes, and command 
buttons. It is a simple yet effective method of requesting information from the user. After the 
text boxes have been filled in, the user clicks OK, and the information is added to a sheet (see Figure 10.8).
Private Sub btn_EmpOK_Click()
Dim LastRow As Long
LastRow = Worksheets("Employee").Cells(Worksheets("Employee").Rows.Count, 1) _
.End(xlUp).Row + 1
Cells(LastRow, 1).Value = tb_EmpName.Value
Cells(LastRow, 2).Value = tb_EmpPosition.Value
Cells(LastRow, 3).Value = tb_EmpHireDate.Value
End SubWith a change in code as shown in the following sample, the same form design can be used to retrieve information. The following code sample retrieves the position and hire date after the employee name has been entered:
Private Sub btn_EmpOK_Click()
Dim EmpFound As Range
With Range("EmpList") 'a named range on a sheet listing the employee names
Set EmpFound = .Find(tb_EmpName.Value)
If EmpFound Is Nothing Then
MsgBox ("Employee not found!")
tb_EmpName.Value = ""
Exit Sub
Else
With Range(EmpFound.Address)
tb_EmpPosition = .Offset(0, 1)
tb_HireDate = .Offset(0, 2)
End With
End If
End With
End SubThe available events for Label, TextBox, and CommandButton controls are described in Table 10.2.
Table 10.2. The Events for Label, TextBox, and CommandButton Controls
Description | |
|---|---|
| Occurs after the control’s data has been changed by the user. |
| Occurs while the user drags and drops data onto the control. |
| Occurs right before the user is about to drop or paste data into the control. |
| Occurs before the data in the control is changed. |
| Occurs when the value of the control is changed. |
Occurs when the user clicks the control with the mouse. | |
| Occurs when the user double-clicks the control with the mouse. |
| Occurs when the user presses F4 on the keyboard. This is similar to the drop-down control on the combo box, but there is no drop-down on a text box. |
Occurs right before the control receives the focus from another control on the same userform. | |
| Occurs when the control runs into an error and can’t return the error information. |
Occurs right after the control loses focus to another control on the same userform. | |
Occurs when the user presses a key on the keyboard. | |
Occurs when the user presses an ANSI key. An ANSI key is a typeable character, such as the letter A. A nontypeable character would be, for example, the Tab key. | |
Occurs when the user releases a key on the keyboard. | |
| Occurs when the user presses the mouse button within the borders of the control. |
| Occurs when the user moves the mouse within the borders of the control. |
| Occurs when the user releases the mouse button within the borders of the control. |
[2] [1] [3] | |
You can let users type in an employee name to search for, but what if they misspell the name? You need a way of making sure that the name is entered correctly. Which do you use: a list box or a combo box?
| |
|
|
In this case, where we want to limit user options, we use a list box to list the employee names, as shown in Figure 10.9.
In the RowSource property of the list box, enter the range from which the control should draw its data. Use a dynamic named range to keep the list updated if employees are added:
Private Sub btn_EmpOK_Click()
Dim EmpFound As Range
With Range("EmpList")
Set EmpFound = .Find(lb_EmpName.Value)
If EmpFound Is Nothing Then
MsgBox ("Employee not found!")
lb_EmpName.Value = ""
Exit Sub
Else
With Range(EmpFound.Address)
tb_EmpPosition = .Offset(0, 1)
tb_HireDate = .Offset(0, 2)
End With
End If
End With
End SubList boxes have a MultiSelect property, which allows the user to select multiple items from the choices in the list box, as shown in Figure 10.10:
fmMultiSelectSingle—. The default setting allows only a single item selection at a time.fmMultiSelectMulti—. Allows an item to be deselected by clicking it again; multiple items can also be selected.fmMultiSelectExtended—. Allows the Ctrl and Shift keys to be used to select multiple items.
If multiple items are selected, the Value property cannot be used to retrieve the items. Instead, check whether the item is selected, and then manipulate it as needed:
Private Sub btn_EmpOK_Click()
Dim LastRow As Long, i As Integer
LastRow = Worksheets("Sheet2").Cells(Worksheets("Sheet2").Rows.Count, 1) _
.End(xlUp).Row + 1
Cells(LastRow, 1).Value = tb_EmpName.Value
'check the selection status of the items in the ListBox
For i = 0 To lb_EmpPosition.ListCount - 1
'if the item is selected, add it to the sheet
If lb_EmpPosition.Selected(i) = True Then
Cells(LastRow, 2).Value = Cells(LastRow, 2).Value & _
lb_EmpPosition.List(i) & ","
End If
Next i
Cells(LastRow, 3).Value = tb_HireDate.Value
End SubThe items in a list box start counting at zero; so if you use the ListCount property, you must subtract one from the result:
For i = 0 To lb_EmpPosition.ListCount - 1
The available events for ListBox controls and ComboBox controls are described in Table 10.3.
Table 10.3. Events for ListBox and ComboBox Controls
Event | Description |
|---|---|
| Occurs after the control’s data has been changed by the user. |
| Occurs while the user drags and drops data onto the control. |
| Occurs right before the user is about to drop or paste data into the control. |
| Occurs before the data in the control is changed. |
| Occurs when the value of the control is changed. |
| Occurs when the user selects a value from the list box or combo box. |
| Occurs when the user double-clicks the control with the mouse. |
| Occurs when the drop-down list appears after the user clicks the drop-down arrow of the combo box or presses F4 on the keyboard. |
| Occurs right before the control receives the focus from another control on the same userform. |
| Occurs when the control runs into an error and can’t return the error information. |
| Occurs right after the control loses focus to another control on the same userform. |
| Occurs when the user presses a key on the keyboard. |
| Occurs when the user presses an ANSI key. An ANSI key is a typeable character, such as the letter A. A nontypeable character would be, for example, the Tab key. |
| Occurs when the user releases a key on the keyboard. |
| Occurs when the user presses the mouse button within the borders of the control. |
| Occurs when the user moves the mouse within the borders of the control. |
| Occurs when the user releases the mouse button within the borders of the control. |
[1] | |
| Option buttons are similar to check boxes in that they can be used to make a selection. But, unlike check boxes, option buttons can be easily configured to allow only one selection out of a group. |
| Using the Frame tool, draw a frame to separate the next set of controls from the other controls on the userform. The frame is used to group option buttons together, as shown in Figure 10.11. |
Option buttons have a GroupName property. If you assign the same group name, Buildings, to a set of option buttons, you force them to act collectively as a toggle, so that only one button in the set can be selected. Selecting an option button automatically deselects the other buttons in the same group or frame. To prevent this behavior, either leave the GroupName property blank or enter another name.
Tip
For users who prefer to select the option button’s label rather than the button itself, add code to the label to trigger the option button.
Private Sub Lbl_Bldg1_Click() Obtn_Bldg1.Value = True End Sub
The available events for OptionButton controls and Frame controls are described in Table 10.4.
Table 10.4. Events for OptionButton and Frame Controls
Event | Description |
|---|---|
| Occurs after the control’s data has been changed by the user. |
| Occurs when a control is added to a frame on a form at runtime. Does not run at design time or upon userform initialization. |
| Occurs while the user does a drag and drop onto the control. |
| Occurs right before the user is about to drop or paste data into the control. |
| Occurs before the data in the control is changed. |
| Occurs when the value of the control is changed. |
| Occurs when the user clicks the control with the mouse. |
| Occurs when the user double-clicks the control with the mouse. |
| Occurs right before the control receives the focus from another control on the same userform. |
| Occurs when the control runs into an error and can’t return the error information. |
| Occurs right after the control loses focus to another control on the same userform. |
| Occurs when the user presses a key on the keyboard. |
| Occurs when the user presses an ANSI key. An ANSI key is a typeable character, such as the letter A. A nontypeable character would be, for example, the Tab key. |
| Occurs when the user releases a key on the keyboard. |
| Occurs when the frame changes size. |
| Occurs when the user presses the mouse button within the borders of the control. |
| Occurs when the user moves the mouse within the borders of the control. |
| Occurs when the user releases the mouse button within the borders of the control. |
| Occurs when a control is deleted from within the frame control. |
| Occurs when the scrollbar box, if visible, is repositioned. |
| Occurs when the zoom value is changed. |
[1] [2] | |
The following code displays the photograph corresponding to the selected employee from the list box:
Private Sub lb_EmpName_Change()
Dim EmpFound As Range
With Range("EmpList")
Set EmpFound = .Find(lb_EmpName.Value)
If EmpFound Is Nothing Then
MsgBox ("Employee not found!")
lb_EmpName.Value = ""
Exit Sub
Else
With Range(EmpFound.Address)
tb_EmpPosition = .Offset(0, 1)
tb_HireDate = .Offset(0, 2)
On Error Resume Next
Img_Employee.Picture = LoadPicture _
("C:Excel VBA 2007 by Jelen & Syrstad" & EmpFound & ".bmp")
On Error GoTo 0
End With
End If
End WithThe available events for Graphic controls are described in Table 10.5.
Table 10.5. Events for Graphic Controls
Event | Description |
|---|---|
| Occurs while the user drags and drops data onto the control. |
| Occurs right before the user is about to drop or paste data into the control. |
| Occurs when the user clicks the image with the mouse. |
| Occurs when the user double-clicks the image with the mouse. |
| Occurs when the control runs into an error and can’t return the error information. |
| Occurs when the user presses the mouse button within the borders of the image. |
| Occurs when the user moves the mouse within the borders of the image. |
| Occurs when the user releases the mouse button within the borders of the control. |
As it is, the Hire Date field allows the user to enter the date in any format: 1/1/1 or January 1, 2001. This possible inconsistency can create problems later on if you need to use or search for dates. The solution? Force users to enter dates in a unified manner.
| Spin buttons allow the user to increment/decrement through a series of numbers. In this way, the user is forced to enter numbers rather than text. |
Draw a spin button for a Month entry on the form. In the Properties, set the Min to 1 (for January) and the Max to 12 (for December). In the Value property, enter 1, the first month. Next, draw a text box next to the spin button. This is the text box that will reflect the value of the spin button. (Labels can also be used.)
Private Sub SpBtn_Month_Change() tb_Month.Value = SpBtn_Month.Value End Sub
Finish building the form. Use a Min of 1 and Max of 31 for days; Min of 1900 and a Max of 2100 for Year:
Private Sub btn_EmpOK_Click()
Dim LastRow As Long, i As Integer
LastRow = Worksheets("Sheet2").Cells(Worksheets("Sheet2").Rows.Count, 1) _
.End(xlUp).Row + 1
Cells(LastRow, 1).Value = tb_EmpName.Value
For i = 0 To lb_EmpPosition.ListCount - 1
If lb_EmpPosition.Selected(i) = True Then
Cells(LastRow, 2).Value = Cells(LastRow, 2).Value & _
lb_EmpPosition.List(i) & ","
End If
Next i
'Concatenate the values from the textboxes to create the date
Cells(LastRow, 3).Value = tb_Month.Value & "/" & tb_Day.Value & __
"/" & tb_Year.Value
End SubThe available events for SpinButton controls are described in Table 10.6.
Table 10.6. Events for SpinButton Controls
Event | Description |
|---|---|
| Occurs after the control’s data has been changed by the user. |
| Occurs while the user drags and drops data onto the control. |
| Occurs right before the user is about to drop or paste data into the control. |
| Occurs before the data in the control is changed. |
| Occurs when the value of the control is changed. |
| Occurs when the user double-clicks the control. |
| Occurs right before the control receives the focus from another control on the same userform. |
| Occurs when the control runs into an error and can’t return the error information. |
| Occurs right after the control loses focus to another control on the same userform. |
| Occurs when the user presses a key on the keyboard. |
| Occurs when the user presses an ANSI key. An ANSI key is a typeable character, such as the letter A. A nontypeable character would be, for example, the Tab key. |
| Occurs when the user releases a key on the keyboard. |
| Occurs when the user clicks the lower or left spin button, decreasing the value. |
| Occurs when the user clicks the upper or right spin button, increasing the value. |
| The |
Tip
Multipage forms should be planned from the beginning—adding them after the rest of the form is created is not an easy task. If you decide at a later point you need a multipage form, insert a new form, draw the multipage, and copy/paste the controls from the other forms to the new form.
Note
Unlike the other controls, you can’t right-click the MultiPage control and view code. Instead, select the control and press F7 on the keyboard or go to View, Code.
You can modify a page by right-clicking the page and bringing up a menu of options: Insert a New Page, Delete the Page You Right-Clicked On, Rename a Page, or Move a Page.
Unlike many of the other controls where the Value property holds a user-entered or selected value, the Value property of the MultiPage control holds the number of the active page, starting a zero. For example, if you have a five-page form and want to activate the fourth page, do this:
MultiPage1.Value = 4
If you have a control you want all the pages to share—such as Save or Cancel buttons—place the control on the main userform rather than on the individual pages, as shown in Figure 10.14.
The available events for MultiPage controls are described in Table 10.7.
Table 10.7. Events for the MultiPage Control
Event | Description |
|---|---|
| Occurs when a control is added to a page of the |
| Occurs while the user drags and drops data onto a page of the |
| Occurs right before the user is about to drop or paste data onto a page of the |
| Occurs when the user changes pages of a multipage. |
| Occurs when the user clicks on a page of the |
| Occurs when the user double-clicks a page of the |
| Occurs right before the multipage receives the focus from another control on the same userform. |
| Occurs when the |
| Occurs right after the multipage loses focus to another control on the same userform. |
| Occurs when the user presses a key on the keyboard. |
| Occurs when the user presses an ANSI key. An ANSI key is a typeable character, such as the letter A. A nontypeable character would be, for example, the Tab key. |
| Occurs when the user releases a key on the keyboard. |
| Occurs when the user presses the mouse button within the borders of the control. |
| Occurs when the user moves the mouse within the borders of the control. |
| Occurs when the user releases the mouse button within the borders of the control. |
| Occurs when a control is removed from a page of the multipage. |
| Occurs when the scrollbar box, if visible, is repositioned. |
| Occurs when the zoom value is changed. |
Even if users are told to fill in all the fields, there is no way to force them to do so—except with an electronic form. As a programmer, you can ensure that all required fields are filled in by not allowing the user to continue until all requirements are met:
If tb_EmpName.Value = "" Then
frm_AddEmp.Hide
MsgBox ("Please enter an Employee Name")
frm_AddEmp.Show
Exit Sub
End IfThe userforms created in the VB Editor are not that different from normal windows: They also include the X close button in the upper-right corner. Although using the button is not wrong, it can cause problems, depending on the objective of the userform. In cases like this, you might want to control what happens if the user presses the button. Use the QueryClose event of the userform to find out what method is used to close the form and code an appropriate action:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
MsgBox "Please use the OK or Cancel buttons to close the form", vbCritical
Cancel = True
End If
End SubAfter you know the method the user used to try and close the form, you can create a message box similar to Figure 10.15 to warn the user that the method was illegal.
The QueryClose event can be triggered in three other ways:
vbFormCode—. TheUnloadstatement was used.vbAppWindows—. Windows shuts down.vbAppTaskManager—. The application was shut down by the Task Manager.
One of the most common client interactions is when you need the client to specify a path and filename. Excel VBA has a built-in function to display the File Open dialog box, as shown in Figure 10.16. The client browses to and selects a file. When the client chooses the Open button, Excel VBA does not open the file, but instead returns the selected file to you.
Sub SelectFile()
' Ask which file to copy
x = Application.GetOpenFilename( _
FileFilter:="Excel Files (*.xls*), *.xls*", _
Title:="Choose File to Copy", MultiSelect:=False)
' check in case no files were selected
If x = "False" Then Exit Sub
MsgBox "You selected " & x
End SubThe above code will allow the client to select one file. If you want them to specify multiple files, use this code:
Sub ManyFiles()
Dim x As Variant
x = Application.GetOpenFilename( _
FileFilter:="Excel Files (*.xls*), *.xls*", _
Title:="Choose Files", MultiSelect:=True)
' check in case no files were selected
On Error Resume Next
If x = "False" Then Exit Sub
On Error GoTo 0
For i = 1 To UBound(x)
MsgBox "You selected " & x(i)
Next i
End SubIn a similar fashion, you can use Application.GetSaveAsFileName to find the path and filename that should be used for saving a file.