In this chapter, you will:
Use an input box to request user input
Use a message box to display information
Learn how to create a userform
Add controls to the userform
Verify a required field has an entry
Prevent a user from closing a form
Prompt the user to select a file
Userforms enable you to display information and allow the user to input information. Using InputBox
and MsgBox
controls are simple ways of doing this. You can use the userform controls in the VB Editor to create forms that are more complex.
This chapter covers simple user interfaces using input boxes and message boxes and the basics of creating userforms in the VB Editor.
The InputBox
function is used to create a basic interface element that requests input from the user before the program can continue. You can configure the prompt, the title for the window, a default value, the window position, and user help files. The only two buttons provided are the OK and Cancel buttons. The returned value is a string.
The following code asks the user for the number of months to be averaged. Figure 10-1 shows the resulting input box.
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
has only OK and Cancel buttons, the MsgBox
function enables you to choose from several configurations of buttons, including Yes, No, OK, and Cancel. You also can configure the prompt, the window title, and help files. The following code produces a prompt to find out whether the user wants to continue. You use a Select Case
statement to continue the program with the appropriate action:
myTitle = "Report Finalized" MyMsg = "Do you want to save changes and close?" Response = MsgBox(myMsg, vbExclamation + vbYesNoCancel, myTitle) Select Case Response Case Is = vbYes ActiveWorkbook.Close SaveChanges:=True Case Is = vbNo ActiveWorkbook.Close SaveChanges:=False Case Is = vbCancel Exit Sub End Select
Figure 10-2 shows the resulting customized message box.
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 selecting Insert, UserForm from the main menu. When a UserForm module is added to the Project Explorer, a blank form appears in the window where your code usually is, and the Controls toolbox appears. If the toolbox does not appear, select View, Toolbox from the main menu.
To change the codename of the userform, select the form and change the (Name
) property from the Properties window. The codename of a userform is used to refer to the form, as shown in the following sections. You can resize a userform by grabbing and dragging the handles on its right side, bottom edge, or lower-right corner. To add controls to the form, click the desired control in the toolbox and draw it on the form. You can move and resize controls at any time.
After you add a control to a form, you can change its properties from the Properties window. (Or, if you don’t want to set the properties manually now, you can set them 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. The syntax FormName.Show
causes a form for the user to pop up:
frm_AddEmp.Show
The Load
method can also be used to call a userform to place it in memory. It allows a form to be loaded while remaining hidden:
Load frm_AddEmp
To hide a userform, use the Hide
method. When you do, the form is still active but is hidden from the user. However, the controls on the form can still be accessed programmatically:
frm_AddEmp.Hide
The Unload
method unloads a form from memory and removes it from the user’s view, which means the form cannot be accessed by the user or programmatically:
Unload Me
The code for a control goes in the form’s module. Unlike with the other modules, double-clicking the form’s module opens the form in Design view. To view the code, you can right-click either the module or the userform in Design view and select View Code.
Just like a worksheet, a userform has events that are triggered by actions. After the userform has been added to a project, the events are available in the Properties drop-down menu at the top right of the code window (see Figure 10-5); to access them, select UserForm from the Object drop-down menu on the left.
The available events for userforms are described in Table 10-1.
TABLE 10-1 Userform events
Event | Description |
---|---|
| Occurs when a userform is either loaded or shown. 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. If a click event is also in use, the double-click event will not work. |
| Occurs when a userform is deactivated. |
| Occurs when the userform runs into an error and cannot 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. An example of a nontypeable character is 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 a visible scrollbar box is repositioned. |
| Occurs after the userform has been unloaded. This is triggered after |
| Occurs when the zoom value is changed. |
To program a control, highlight the control and select View, Code. The footer, header, and default action for the control are entered in the programming field automatically. To see the other actions that are available for a control, select the control from the Object drop-down menu and view the actions in the Properties drop-down menu, as shown in Figure 10-6.
The controls are objects, like ActiveWorkbook
. They have properties and methods that depend on the type of control. Most of the programming for the controls is done in the form’s module. However, if another module needs to refer to a control, the parent, which is the form, needs to be included with the object. Here’s an example of a button event that closes the form:
Private Sub btn_EmpCancel_Click() Unload Me End Sub
The preceding code can be broken down into three sections:
Btn_EmpCancel
—Name given to the control.
Click
—Action of the control.
Unload Me
—Code behind the control, which, in this case, is unloading the form.
Each control has different events associated with it, so you can 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.
A label control is used to display text with information for the user.
A text box control is used to get a manual entry from the user.
A command button control is used to create a button a user can press to have the program perform an action.
The basic form shown in Figure 10-7 consists of labels, text boxes, and command buttons. Using such a form is a simple yet effective method of requesting information from the user. After the text boxes have been filled in, the user clicks OK, your code reformats the data, if needed, and then adds the information to a sheet (see Figure 10-8), as shown in the following code:
Private Sub btn_EmpOK_Click() Dim LastRow As Long Dim tblEmployees As ListObject Set tblEmployees = Worksheets("Employees").ListObjects("tblEmployees") With tblEmployees .ListRows.Add 'add a new row With .DataBodyRange LastRow = .Rows.Count 'get the new row .Cells(LastRow, 1).Value = tb_EmpName.Value .Cells(LastRow, 2).Value = tb_EmpPosition.Value .Cells(LastRow, 3).Value = tb_EmpHireDate.Value End With End With Set tblEmployees = Nothing End Sub
By changing the code as shown in the following sample, you can use the same form design to retrieve information. The following code retrieves the position and hire date after the employee’s name is entered:
Private Sub btn_EmpOK_Click() Dim EmpFound As Range Dim tblEmployees As ListObject Set tblEmployees = Worksheets("Employees").ListObjects("tblEmployees") With tblEmployees.ListColumns("Name").DataBodyRange Set EmpFound = .Find(tb_EmpName.Value) If EmpFound Is Nothing Then Msgbox ("Employee not found!") tb_EmpName.Value = "" Else With .Cells(EmpFound.Row - 1, 1) tb_EmpPosition = .Offset(0, 1) tb_HireDate = .Offset(0, 2) End With End If End With Set EmpFound = Nothing Set tblEmployees = Nothing End Sub
EmpFound
returns the location of the match as it pertains to the sheet, not the listobject. To return the correct location as it pertains to the listobject’s databodyrange, subtract 1 from Emfound.Row
.
The available events for Label
, TextBox
, and CommandButton
controls are described in Table 10-2.
TABLE 10-2 Label
, TextBox
, and CommandButton
control events
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 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 feature 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 cannot 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. An example of a nontypeable character is 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 TextBox
control only
2 Label
and CommandButton
controls
3 TextBox
and CommandButton
controls
You can let users type employee names to search for, but what if they misspell a name? You need a way to make sure that names are typed correctly. Which do you use for this, a list box or a combo box? As explained below, the two are similar, but the combo box has an additional feature that you may or may not need.
A list box displays a list of values from which the user can choose.
A combo box displays a list of values from which the user can choose and allows the user to enter a new value.
In this case, when you want to limit user options, you should use a list box to list the employee names, as shown in Figure 10-9.
Use the following Initialize
event to fill the list box with names:
Private Sub UserForm_Initialize() Dim tblEmployees As ListObject Set tblEmployees = Worksheets("Employees").ListObjects("tblEmployees") Me.lb_EmpName.RowSource = tblEmployees.ListColumns(1).DataBodyRange.Address Set tblEmployees = Nothing End Sub
Use the Click
event to fill in the position and hire date fields when a name is selected:
Private Sub lb_EmpName_Click() Dim EmpFound As Range Dim tblEmployees As ListObject Set tblEmployees = Worksheets("Employees").ListObjects("tblEmployees") With tblEmployees.ListColumns("Name").DataBodyRange Set EmpFound = .Find(lb_EmpName.Value) With .Cells(EmpFound.Row - 1, 1) tb_EmpPosition.Value = .Offset(0, 1) tb_HireDate.Value = .Offset(0, 2) End With End With Set EmpFound = Nothing Set tblEmployees = Nothing End Sub
MultiSelect
property of a list boxList boxes have a MultiSelect
property, which enables the user to select multiple items from the choices in the list box (see Figure 10-10):
fmMultiSelectSingle
—The default setting allows only a single item selection at a time.
fmMultiSelectMulti
—This allows an item to be deselected when it is clicked again; multiple items can also be selected.
fmMultiSelectExtended
—This 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 to see whether the item is selected and then manipulate it as needed. The following code processes the job positions list box, adding selected positions to the table:
Private Sub btn_EmpOK_Click() Dim LastRow As Integer, i As Integer Dim tblEmployees As ListObject Set tblEmployees = Worksheets("Employees").ListObjects("tblEmployees") With tblEmployees .ListRows.Add 'add a new row With .DataBodyRange LastRow = .Rows.Count 'get the new row .Cells(LastRow, 1).Value = tb_EmpName.Value 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 'remove excess comma .Cells(LastRow, 2).Value = Left(.Cells(LastRow, 2).Value, _ Len(.Cells(LastRow, 2).Value) - 1) End With End With Set tblEmployees = Nothing End Sub
The items in a list box start counting at zero. For this reason, if you use the ListCount
property, you must subtract one from the result:
For i = 0 To lb_EmpPosition.ListCount - 1
The available events for ListBox
controls and ComboBox
controls are described in Table 10-3.
TABLE 10-3 ListBox
and ComboBox
control events
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 menu 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. An example of a nontypeable character is 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 ComboBox
control only
Option buttons are similar to check boxes in that they can be used to make selections. However, unlike check boxes, option buttons can be configured to allow only one selection out of a group.
Using the Frame tool, draw a frame to separate the next set of controls from the other controls on the userform. The frame is used to group option buttons together, as shown in Figure 10-11.
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 a unique name for each option button.
The available events for OptionButton
controls and Frame
controls are described in Table 10-4.
TABLE 10-4 OptionButton
and Frame
control events
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 cannot 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. An example of a nontypeable character is 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 OptionButton
control only
2 Frame
control only
A list on a form can be even more helpful if a corresponding graphic is added to the form. The following code displays a photograph corresponding to the selected employee from the list box:
Private Sub lb_EmpName_Change() Dim EmpFound As Range Dim tblEmployees As ListObject Set tblEmployees = Worksheets("Employees").ListObjects("tblEmployees") With tblEmployees Set EmpFound = .ListColumns("Name").DataBodyRange.Find(lb_EmpName.Value) If EmpFound Is Nothing Then MsgBox "Employee not found!" lb_EmpName.Value = "" Exit Sub Else With .DataBodyRange.Cells(EmpFound.Row - 1, .ListColumns("Name").Index) tb_EmpPosition = .Offset(0, 1).Value tb_HireDate = .Offset(0, 2).Value On Error Resume Next Img_Employee.Picture = LoadPicture _ (ThisWorkbook.Path & "" & EmpFound.Value & ".jpg") On Error GoTo 0 End With End If End With Set EmpFound = Nothing Set tblEmployees = Nothing End Sub
The available events for Graphic
controls are described in Table 10-5.
TABLE 10-5 Graphic
control events
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. |
In the example we’ve been working with, the Hire Date field allows the user to enter the date in any format, such as 1/1/18 or January 1, 2018. 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 window, set Min
to 1
for January and Max
to 12
for December. For the Value
property, enter 1
, the first month. Next, draw a text box next to the spin button. This text box reflects the value of the spin button. Place the code below behind the month’s spin button control:
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 Day, and a Min
of 1900
and a Max of 2100
for Year:
Private Sub btn_EmpOK_Click() Dim LastRow As Integer, i As Integer Dim tblEmployees As ListObject Set tblEmployees = Worksheets("Employees").ListObjects("tblEmployees") If tb_EmpName.Value = "" Then frm_AddEmp.Hide MsgBox ("Please enter an Employee Name") frm_AddEmp.Show Exit Sub End If With tblEmployees .ListRows.Add 'add a new row With .DataBodyRange LastRow = .Rows.Count 'get the new row .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 With End With End Sub
The available events for SpinButton
controls are described in Table 10-6.
TABLE 10-6 SpinButton
control events
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 right before the control receives the focus from another control on the same userform. |
| Occurs when the control runs into an error and cannot 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. An example of a nontypeable character is 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. |
MultiPage
control to combine forms The MultiPage
control provides a neat way of organizing multiple forms. Instead of having one form for personal employee information and one for on-the-job information, combine the information into one multipage form, as shown in Figures 10-12 and 10-13.
You can modify a page by right-clicking the tab of the page and then choosing from the following menu options: New Page, Delete Page, Rename, and Move.
Unlike many of the other controls in which the Value
property holds a user-entered or user-selected value, the MultiPage
control uses the Value
property to hold the number of the active page, starting at zero. For example, if you have a five-page form and want to activate the fourth page, use this:
MultiPage1.Value = 3
If you have a control you want all the pages to share, such as a Save, Cancel, or Close button, 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 MultiPage
control events
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 |
| Occurs when the user clicks on a page of the |
| Occurs when the user double-clicks a page of the |
| Occurs right before the |
| Occurs when the |
| Occurs right after 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. An example of a nontypeable character is the Tab key. |
| Occurs when the user releases a key on the keyboard. |
| Occurs when the |
| 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 |
| Occurs when the scrollbar box, if visible, is repositioned. |
| Occurs when the zoom value is changed. |
Even when users are told to fill in all the fields, they don’t always do it. With a paper form, there is no way to force them to do so. As a programmer, you can ensure that all required fields are filled in by not allowing the user to continue until all requirements are met. Here’s how to do this:
If tb_EmpName.Value = "" Then frm_AddEmp.Hide MsgBox "Please enter an Employee Name" frm_AddEmp.Show Exit Sub End If
The userforms created in the VB Editor are not that different from normal dialog boxes; 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 clicks 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 'prevent the form from closing End If End Sub
When you know which method the user used to try to close the form, you can create a message box similar to the one shown in Figure 10-15 to warn the user that the method was illegal.
The QueryClose
event can be triggered in four ways:
vbFormControlMenu
—The user either right-clicks on the form’s title bar and selects the Close command or clicks the X in the upper-right corner of the form.
vbFormCode
—The Unload
statement is used.
vbAppWindows
—Windows shuts down.
vbAppTaskManager
—The application is shut down by the Task Manager.
One of the most common client interactions occurs when you need the client to specify a path and file name. 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 clicks the Open button, instead of opening the file, Excel VBA returns the full path and file name to the code:
Sub SelectFile() Dim x As String '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 Sub
The preceding code allows the client to select one file. If you want the user to specify multiple files, use this code:
Sub ManyFiles() Dim x As Variant Dim i As Integer x = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", _ Title:="Choose Files", MultiSelect:=True) On Error Resume Next If Ubound(x) > 0 Then For i = 1 To UBound(x) MsgBox "You selected " & x(i) Next i ElseIf x = "False" Then Exit Sub End If On Error GoTo 0 End Sub
In a similar fashion, you can use Application.GetSaveAsFileName
to find the path and file name that should be used to save a file.
Userforms allow you to get information from the users and guide them on how to provide the program with that information. In Chapter 11, “Data mining with Advanced Filter,” you’ll find out about using Advanced Filter to produce reports quickly.