CHAPTER 10
Userforms: An introduction
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.
Note To learn more about advanced userform programming, see Chapter 22, “Advanced userform techniques.”
Input boxes
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")
FIGURE 10-1 An input box can be simple but still effective.
Tip If you need to force the entry of a variable type other than string, use Application.InputBox. This method allows you to specify the return data type, including a formula, number, or cell reference.
Message boxes
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.
FIGURE 10-2 The MsgBox function is used to display information and obtain a basic response from the user.
Tip You can combine an icon option and a buttons option for the buttons argument by separating them with the plus (+) symbol. In the previous example,vbExclamation + vbYesNoCancel instructed Excel to show the exclamation symbol and the Yes, No, and Cancel buttons.
Creating a userform
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).
FIGURE 10-3 Create a custom userform to get more information from the user.
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.
To change the codename of the userform, select the form and change the (Name) property. 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.
Note By default, the toolbox displays the most common controls. To access more controls, right-click the toolbox and select Additional Controls. However, be careful; other users might not have the same additional controls as you do. If you send users a form with a control they do not have installed, the program generates an error.
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.
FIGURE 10-4 Use the Properties window to change the properties of a control.
Calling and hiding a userform
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
TIP Me is a keyword that can be used to refer to the userform. It can be used in the code of any control to refer to itself.
Programming userforms
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.
Userform events
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.
FIGURE 10-5 Various events for a userform can be selected from the drop-down menu at the top of the code window.
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 Activate event. |
|
Occurs when the user presses a key on the keyboard. |
|
Occurs when the user presses an ANSI key. An ANSI key is a typable character such as the letter A. An example of a nontypable 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 formand 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. |
Programming controls
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.
FIGURE 10-6 You can select various actions for a control from the VB Editor drop-down menus.
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 controlClick—Action of the controlUnload Me—Code behind the control, which, in this case, is unloading the form
Tip Change the (Name) property in the control’s Properties window to rename a control from the default assigned by the editor.
Case study: Bug fix when adding controls to an existing form
If you’ve been using a userform for some time and later try to add a new control, you might find that Excel seems to get confused about the control. You will see that the control is added to the form, but when you right-click the control and select View Code, the code module does not seem to acknowledge that the control exists. The control name is not available in the left drop-down menu at the top of the code module.
To work around this situation, follow these steps:
Add all the controls you need to add to the existing userform.
In the Project Explorer, right-click the userform and select Export File. Select Save to save the file in the default location.
In the Project Explorer, right-click the userform and select Remove. Because you just exported the userform, click No to the question about exporting.
Right-click anywhere in the Project Explorer and select Import File. Select the file name that you saved in step 2.
The new controls are now available in the code window of the userform.
Using basic form controls
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. |
Using labels, text boxes, and command buttons
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, and your code reformats the data, if needed, then adds the information to a sheet (see Figure 10-8), as shown in the following code:
FIGURE 10-7 You can use a simple form like this to collect information from the user.
FIGURE 10-8 The information from the form is added to the sheet.
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 controlon 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 user-form. |
|
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 typable character such as the letter A. An example of a nontypable 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. |
1TextBox control only
2Label and CommandButton controls
3TextBox and CommandButton controls
Deciding whether to use list boxes or combo boxes in forms
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.
FIGURE 10-9 You can use a list box to control user input and selections.
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
Using the MultiSelect property of a list box
List boxes have a MultiSelect property, which enables the user to select multiple items from the choices in the list box (see in 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, using the following code:
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
FIGURE 10-10 The MultiSelect property enables the user to select multiple items from a list box.
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 thecombo box or presses F4 on the keyboard. |
|
Occurs right before the control receives the focus from another control on the same user-form. |
|
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 typable character such as the letter A. An example of a nontypable 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
Adding option buttons to a userform
 |
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. |
FIGURE 10-11 You can use a frame to group option buttons together.
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.
Note For users who prefer to select the option button’s label rather than the button itself, create a separate label and add code to the label, like this, 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 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 timeor 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 user-form. |
|
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 typable character such as the letter A. An example of a nontypable 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
Adding graphics to a userform
 |
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)
tb_HireDate = .Offset(0, 2)
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. |
Using a spin button on a userform
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. In addition, you can use labels. 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, or 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 sameuserform. |
|
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 typable character such as theletter A. An example of a nontypable 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. |
Using the 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.
FIGURE 10-12 Use the MultiPage control to combine multiple forms. This is the first page of the form.
FIGURE 10-13 This is the second page of the form.
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.
Tip Adding multipage forms after the rest of the form has been created is not an easy task. Therefore, plan multipage forms from the beginning. If you decide later that you need a multipage form, insert a new form, draw the MultiPage control, and copy/paste the controls from the other forms to the new form.
Note Do not right-click in the tab area to view the MultiPage code. Instead, right-click in the MultiPage control’s main area to get the View Code option.
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.
FIGURE 10-14 Place common controls such as the Close button on the main userform.
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 typable character, such as the letter A. An example of a nontypable 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. |
Verifying field entry
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
Illegal window closing
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.
FIGURE 10-15 You can control what happens when the user clicks the X button.
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—TheUnloadstatement is used.vbAppWindows—Windows shuts down.vbAppTaskManager—The application is shut down by the Task Manager.
Getting a file name
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:
FIGURE 10-16 Use the File Open dialog box to allow the user to select a file.
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 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
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.
Next steps
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.