To create a multipage dialog box, click the MultiPage icon in the Toolbox, and then click in the user form where you want the control to appear. The VBA Editor places a MultiPage control with two pages, whose tabs have the labels Page 1 and Page 2. You can then move and size the control as usual. In typical usage, you'll want to create a MultiPage control that's only a little smaller than the user form it inhabits (like most of the multipage dialog boxes you'll see in Windows applications).

Once you've created a MultiPage control, you work with a page on it by right-clicking its tab and using the resulting context menu:

Figure 15.5. Use the Rename dialog box to set the caption, accelerator key, and control-tip text for a page.

Figure 15.6. Use the Move Up and Move Down buttons in the Page Order dialog box to change the order of pages in a MultiPage control.

Once you've created a multipage dialog box, you can populate its pages with controls using the techniques you learned in Chapter 14. Each control must have a unique name within the dialog box (not just within the page on which it appears).

When designing a multipage dialog box, keep the following issues in mind:

Because each control in a multipage dialog box has a unique name, when returning information from a multipage dialog box you need specify only the relevant object—you don't need to specify which page it's on.

Figure 15.7 shows an example of a multipage dialog box. The first page contains the customer's personal contact information; the second, the customer's professional information; the third, the associations the customer belongs to; and the fourth, the certifications the customer holds.

Most of the properties of the MultiPage control are straightforward, but a few deserve special mention:

Figure 15.7. By using multiple pages in a dialog box, you can achieve a clean and uncluttered look that's also easily navigable.

The MultiPage control doesn't have to take up the whole dialog box—in fact, most dialog boxes keep the key command buttons outside the multipage area so that they're available to the user no matter which page the user is on. That said, it is usually a good idea to make a MultiPage control the dominant part of a dialog box. In a complex and busy dialog box, a small MultiPage control can appear to be little more than a group box, and the user may miss the tabs, particularly if they're just skimming the controls looking for a particular option.

Dialog boxes that use a tab strip are substantially different from multipage dialog boxes. A TabStrip control is used not to rearrange other controls but to change the data that appears in them as the user moves from one set of data to another.

For instance, you might use a dialog box driven by a tab strip to view and update the records in a data source such as a Word table, an Excel spreadsheet, or an Access database. This example uses an Excel workbook in which information is stored on a number of worksheets. Figure 15.8 shows the DataSurfer dialog box, which is driven by a tab strip.

Figure 15.8. Using a TabStrip control to create a multitab dialog box. The tab strip is used to control which set of information is displayed in the other controls in the dialog box.

The actual strip of tabs in a TabStrip control can appear above, below, or beside the controls that it contains. Above is the conventional—and default—position, just as it is in real-world recipe card boxes and file drawers. But vertical and bottom tabs have shown up in eccentric Windows applications from time to time. As with the MultiPage control, use the TabOrientation property of the TabStrip control to specify whether the tab strip should appear at the top, bottom, left, or right of its control. But be sure to have some pretty good reason if you're departing from convention.

The tab strip can contain zero, one, or more tabs. For most purposes, there's little point in having only one tab on a tab strip, and even less in having no tab at all. But if you dynamically populate the tab strip with tabs in your procedures (as you're about to do in this next example) and create one tab for each record found, you may run into situations with only one record and thus a dialog box with only one tab—or even a tab strip without any tabs at all.

Click the TabStrip button on the Toolbox, click in the user form to place the TabStrip, and then drag it to an appropriate size. Bear in mind that a TabStrip is only a visual display for the user's benefit. Unlike the MultiPage control, you establish the logical connection between the TabStrip and the other controls through code. You can then add, rename, move, and delete tabs in the same way as you can pages in a MultiPage control.

If you haven't placed the other controls for the dialog box, you would then do so.

Once everything's in place, you write the code that will enable the tab strip to display the contents of the other controls. Listing 15.2 shows the code for the tab strip in the DataSurfer dialog box. This tab strip is named tabSurfer, and the code works with its Change event—the event that fires when the user clicks a new tab on the strip.

1.  Private Sub tabSurfer_Change()
 2.      If blnInitializing = False Then
 3.          With ActiveWorkbook.Sheets(tabSurfer.Value + 1)
 4.              'load the contents of the worksheet that corresponds _
                  to the tab chosen
 5.              .Activate
 6.              txtFirstName.Text = .Cells(1, 2).Text
 7.              txtInitial.Text = .Cells(2, 2).Text
 8.              txtLastName.Text = .Cells(3, 2).Text
 9.              txtAddress1.Text = .Cells(4, 2).Text
10.              txtAddress2.Text = .Cells(5, 2).Text
11.              txtCity.Text = .Cells(6, 2).Text
12.              txtState.Text = .Cells(7, 2)
13.              txtZip.Text = .Cells(8, 2).Text
14.              txtHomeArea.Text = .Cells(9, 2).Text
15.              txtHomePhone.Text = .Cells(10, 2).Text
16.              txtWorkArea.Text = .Cells(11, 2).Text
17.              txtWorkPhone.Text = .Cells(12, 2).Text
18.              txtWorkExtension.Text = .Cells(13, 2).Text
19.              txtEmail.Text = .Cells(14, 2).Text
20.          End With
21.      End If
22.  End Sub

After specifying the worksheet, the code in Listing 15.2 essentially repeats itself for each of the text boxes that appears in the DataSurfer dialog box. This dialog box works with a data source implemented as Excel spreadsheets in the active workbook.

Each worksheet in the workbook is one customer's record, with the name of the customer appearing on the worksheet's tab and the customer's data appearing in the second column: the first name in the first cell of the second column, the middle initial in the second cell, the last name in the third cell, and so on for the address, phone numbers (both home and work), and email address. So to get at any piece of information, you need to know the sheet of the record in question and the appropriate cell in the second column.

Here's how the code works:

You can add a picture to a dialog box by using an Image control. Click the Image button in the Toolbox, and then click in the user form where you want the Image control to appear. Once you've placed the Image control, you can size and move the picture just as you would any other control.

To choose the picture that will appear in the Image control, select the Picture property in the Properties window and click the ellipsis button that then appears to the right of the entry. The VBA Editor displays the Load Picture dialog box. Select the picture file and choose the Open button. The Picture property in the Properties window registers the type of picture you selected (such as (Bitmap)) but not its filename, and the picture appears in the Image control so that you can see if it's an appropriate size.

LoadPicture filename, [WidthDesired], [HeightDesired]

Image1.Picture = LoadPicture("C:
ose.jpg")

Once you've chosen the picture, you have various options for positioning it and formatting it:

Once you've placed, sized, and formatted a picture, there are various possibilities for what you can do with it, such as using a picture's Click event to trigger an action. For example, if you present users with a choice of two formats for a document, display those two formats, and then allow users to click the appropriate picture to make their choice.

The Initialize event occurs when the user form is loaded but before it appears onscreen.

VBA's syntax for the Initialize event is as follows, where userform is a valid UserForm object:

Private Sub userform_Initialize()

Typical uses for the Initialize event include retrieving information—from a database, a set of worksheets, or whatever—that the user form or application needs and assigning information to the controls on the user form (especially ListBox and ComboBox controls, to which you often need to add the information at runtime rather than at design time).

Depending on the style and complexity of your user forms, you may also want to use the Initialize event to resize the user form, resize controls on the user form, display or hide particular controls, and in general make sure the user form is as closely suited as possible to the user's needs before displaying it.

The QueryClose event applies to the UserForm object only. This event fires just before the user form closes.

The syntax for the QueryClose event is as follows:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

Here, Cancel is an integer, typically 0 (zero). A nonzero value prevents the QueryClose event from firing and stops the user form (and the application) from closing.

CloseMode is a value or a constant giving the cause of the QueryClose event. Table 15.5 shows the values and constants for CloseMode.

At first glance, QueryClose may appear to have few uses beyond double-checking that users really want to close a user form that they're attempting to close. For example, if you established that users had entered a lot of data in the user form they were about to close, you might want to check that they hadn't clicked the user form's Close button or Cancel button by mistake, as in the following code fragment for Word:

Private Sub UserForm_QueryClose(Cancel As Integer, _
    CloseMode As Integer)
    'make sure the user wants to close the user form
    'if they have entered information in it
    Select Case CloseMode
        Case 0
            'user has clicked the close button or invoked an Unload statement
            'if text box contains more than 5 characters, ask to save it
            If Len(txtDescription.Text) > 5 Then
                If MsgBox("The Description text box contains " & _
                    "a significant amount of text." & vbCr & _
                    "Do you want to save this text?", vbYesNo + _
                    vbQuestion, "Close Form") <> 0 Then
                    Documents.Add
                    Selection.TypeText txtDescription.Text
                    ActiveDocument.SaveAs _
                        "c:	empTemporary Description.docm"
                    MsgBox "The contents of the Description text " & _
                        "box have been saved in " & _
                        "c:	empTemporary Description.docm.", _
                        vbOKOnly + vbInformation, _
                        "Form Information Saved"
                End If
             End If

However, QueryClose comes into its own when the whole application, rather than just the user form, is closing. If the user form is modeless, users may not be aware that it's still open and that they're about to lose data they've typed into it or options they've selected in it.

Sometimes you may be able to use QueryClose to save information from a user form when the application has stopped responding and is being closed by Windows or the Task Manager. Be warned that QueryClose's record isn't perfect on this—the code sometimes won't run.

To stop an application from closing, set the Cancel property of the QueryClose event to True.

The Activate event fires when the user form becomes the active window. Typically, this means the event fires when the user form is displayed, occurring just after the Initialize event if the user form is loaded by a Show statement rather than a Load statement.

Note that if the user form is loaded by using a Load statement before being displayed with the Show statement, the Initialize event fires after the Load statement. The Activate event, firing after the Show statement, fires later.

However, the Activate event also fires when the user form is reactivated after having been deactivated. For example, if you create a modeless user form with an Activate event procedure, the code is executed each time the user reactivates the user form after having deactivated it (for example, by working in the application window). Likewise, if you display one user form from another and then close the second user form, returning the focus to the first user form and reactivating it, the Activate event fires again.

The syntax for the Activate event is as follows:

Private Sub UserForm_Activate()

The Deactivate event fires when the user form loses the focus after having been the active window, but it doesn't fire when the user form is hidden or unloaded. For example, if you display a user form that contains a Deactivate event procedure and then close the user form, the Deactivate event doesn't fire. However, if you display one user form from another, the Deactivate event for the first user form fires as the focus is transferred to the second user form. With modeless user forms, the Deactivate event is triggered each time the user leaves one user form by clicking on another.

The syntax for the Deactivate event is as follows:

Private Sub UserForm_Deactivate()

See the previous sidebar for details on a bug in using the Deactivate and Activate events in immediate succession.

The Resize event fires when a user form is resized either manually by the user or programmatically by you.

The syntax for the Resize event is as follows:

Private Sub UserForm_Resize()

The main use for the Resize event is to move, resize, display, or hide controls to accommodate the new dimensions of the user form. For example, you might resize a text box so that it occupies most of the width of the user form it lives on (see Figure 15.10) by using code such as that shown in Listing 15.3.

1.  Private Sub cmdWidenForm_Click()
 2.      With frmResize
 3.          If .Width < 451 Then
 4.              .Width = .Width + 50
 5.              If cmdNarrowForm.Enabled = False Then _
                     cmdNarrowForm.Enabled = True
 6.              If .Width > 451 Then _
                     cmdWidenForm.Enabled = False
 7.          End If
 8.      End With
 9.  End Sub
10.
11.  Private Sub cmdNarrowForm_Click()
12.      With frmResize
13.          If .Width > 240 Then
14.              .Width = .Width − 50
15.              If cmdWidenForm.Enabled = False Then _
                     cmdWidenForm.Enabled = True

16.              If .Width < 270 Then _
                     cmdNarrowForm.Enabled = False
17.          End If
18.      End With
19.  End Sub
20.
21.  Private Sub cmdClose_Click()
22.      Unload Me
23.  End Sub
24.
25.  Private Sub UserForm_Resize()
26.      txt1.Width = frmResize.Width − 30
27.  End Sub

Figure 15.10. You can use the Resize event of a user form to resize or reposition the controls it contains.

Listing 15.3 contains four short procedures: one for the Click event of the cmdWidenForm command button, one for the Click event of the cmdNarrowForm command button, one for the Click event of the cmdClose command button, and one for the Resize event of the user form.

The cmdWidenForm_Click procedure shown in lines 1 through 9 increases the width of the user form by 50 points (1 point is 1/72 inch) when the user clicks the Widen Form button, as long as the Width property of the user form is less than 451 points. Line 5 enables the cmdNarrowForm command button if it isn't already enabled. (The cmdNarrowForm command button is disabled when the user form is displayed at its original narrow width.) Line 6 disables the cmdWidenForm command button if the Width property of the user form is more than 451 points.

The cmdNarrowForm_Click procedure shown in lines 11 through 19 narrows the user form by 50 points as long as the Width of the user form is greater than 240 points (its original width), reenabling the cmdWidenForm button if it's disabled and disabling the cmdNarrowForm button if the Width of the user form is less than 270 points.

The cmdClose_Click procedure shown in lines 21 through 23 simply unloads the user form (which it refers to by the Me keyword).

The UserForm_Resize event procedure in lines 25 through 27 sets the Width property of txt1, the text box in the user form, to 30 points less than the Width of the user form. If you step through the code for the user form, you'll notice that the Resize event fires when the size of the user form changes. For example, when line 4 of the cmdWidenForm_Click procedure is executed, execution branches to the Resize event procedure in line 25, and this procedure is executed before the code in line 5.

The Terminate event fires when the user form has been unloaded—or, more precisely, when all references to an instance of the user form have been removed from memory or have gone out of scope.

The syntax for the Terminate event is as follows:

Private Sub UserForm_Terminate()

The Scroll event applies to the Frame control, the MultiPage control, the ScrollBar control, and the UserForm object. This event occurs when the user moves the scroll box (the thumb) on a scroll bar on a frame, multipage control, scroll bar, or user form.

The syntax for the Scroll event varies for the three controls and the UserForm object. The syntax for the Scroll event with the UserForm object is as follows:

Private Sub UserForm_Scroll(ByVal ActionX As MSForms.fmScrollAction, ByVal ActionY
As MSForms.fmScrollAction, ByVal RequestDx As Single, ByVal RequestDy As Single,
ByVal ActualDx As MSForms.ReturnSingle, ByVal ActualDy As MSForms.ReturnSingle)

The syntax for the Scroll event with the ScrollBar control is as follows:

Private Sub scrollbar_Scroll()

The syntax for the Scroll event with the MultiPage control is as follows:

Private Sub multipage_Scroll(index As Long, ActionX As fmScrollAction, ActionY As
fmScrollAction, ByVal RequestDx As Single, ByVal RequestDy As Single, ByVal
ActualDx As MSForms.ReturnSingle, ByVal ActualDy As MSForms.ReturnSingle)

The syntax for the Scroll event with the Frame control is as follows:

Private Sub frame_Scroll(ActionX As fmScrollAction, ActionY As fmScrollAction,
ByVal RequestDx As Single, ByVal RequestDy As Single, ByVal ActualDx As MSForms.
Return Single, ByVal ActualDy As MSForms.ReturnSingle)

In these last three syntax statements, scrollbar is a valid ScrollBar object, multipage is a valid MultiPage object, and frame is a valid Frame object.

Here are the arguments for the Scroll event:

Changing the Zoom property is like using a magnifying glass. The form's controls all grow larger if the Zoom value is greater than 100, or they grow smaller if the value is less than 100. However, the form itself doesn't change size. To change the size of the form, you must adjust its Height and Width properties.

The Zoom event fires when the Zoom property of the object is changed at runtime. The Zoom property can be changed either automatically through code or by the user's manipulating a control that changes the property through code; the user can't change the Zoom property manually.

The Zoom property uses this syntax for the control and the UserForm object:

Private Sub object_Zoom(Percent As Integer)

Here, object is a Frame control or a UserForm object. Percent is an Integer argument used to specify the percentage (from 10 percent to 400 percent) the user form is to be zoomed to. By default, user forms and controls are displayed at 100 percent Zoom—full size.

The Zoom property uses this syntax for the MultiPage control:

Private Sub multipage_Zoom(ByVal Index As Long, Percent As Integer)

Index is the index (name or number) of the Page object in the MultiPage control with which the Zoom event procedure is associated.

Zooming a user form zooms all the controls that are on it. For example, say a user form named frmEventsDemo includes a combo box named cmbZoom that offers a selection of zoom percentages. When the user selects an item in the combo box, the Change event for cmbZoom applies the combo box's Value property to the Zoom property of the user form, zooming it to the percentage selected. Zooming the user form triggers the Zoom event, whose procedure in this example sets the Width and Height of the user form to new values suited to the new zoom percentage:

Private Sub cmbZoom_Change()
'change the size of the controls:
    frmEventsDemo.Zoom = cmbZoom.Value
End Sub
Private Sub UserForm_Zoom(Percent As Integer)
' change the size of the form itself:
    frmEventsDemo.Width = 300 * cmbZoom.Value / 100
    frmEventsDemo.Height = 350 * cmbZoom.Value / 100
End Sub

The Layout event occurs when the size of the frame, multipage control, or user form is changed, either by the user or programmatically (automatically by an autosized control's becoming resized).

By default, the Layout event automatically calculates the new position for any control that has been moved and repaints the screen accordingly. However, you can also use the Layout event for your own purposes if you need to.

The syntax for the Layout event with a Frame control or a UserForm object is as follows:

Private Sub object_Layout()

Here, object is a Frame control or a UserForm object.

The syntax for using the Layout event with a MultiPage control is as follows:

Private Sub multipage_Layout(index As Long)

Here, multipage is a MultiPage control and index is the Page object in the multipage control.

The AddControl event is triggered when a control is added programmatically to a frame control, a multipage control, or the user form at runtime; it isn't triggered when you add a control manually at design time. The event isn't triggered when the user form is initialized unless the Initialize event adds a control to the user form.

The syntax for the AddControl event varies depending on the object or control. The syntax for the UserForm object and the Frame control is as follows:

Private Sub object_AddControl(ByVal Control As MSForms.Control)

Here, object is a UserForm object or Frame control, and Control is the control that's being added.

The syntax for the MultiPage control is as follows:

Private Sub multipage_AddControl(ByVal Index As Long, ByVal Control As MSForms.Control)

Here, Index is the index number or name of the Page object that will receive the control.

For example, the following cmdAddControl_Click procedure adds three option buttons (opt1, opt2, and opt3, respectively) to the frame fraOptions and sets properties for the first option button. (A comment indicates where the code would go on to set properties for the second and third option buttons.) The fraOptions_AddControl event procedure displays a message box giving the number of controls the frame now contains. Because the cmdAddControl_Click procedure adds three controls, the AddControl event fires three times, and the fraOptions_AddControl procedure runs three times:

Private Sub cmdAddControl_click()
    Dim opt1 As OptionButton
    Dim opt2 As OptionButton
    Dim opt3 As OptionButton
    Set opt1 = fraOptions.Controls.Add("Forms.OptionButton.1")
    Set opt2 = fraOptions.Controls.Add("Forms.OptionButton.1")
    Set opt3 = fraOptions.Controls.Add("Forms.OptionButton.1")
    With opt1
        .Left = 10
        .Top = 10

.Name = "optDomestic"
        .Caption = "Domestic"
        .AutoSize = True
        .Accelerator = "D"
    End With
    'set properties for opt2 and opt3 here
End Sub

Private Sub fraOptions_AddControl(ByVal Control As MSForms.Control)
    MsgBox "The frame now contains " & _
        fraOptions.Controls.Count & " controls."
End Sub

The RemoveControl event fires when a control is deleted from a frame control, a MultiPage control, or a user form, either programmatically or manually at runtime. (To remove a control manually, the user would typically use a control built into the user form for that purpose. There has to be some programming here—users can't simply delete controls all by themselves.)

The syntax for the RemoveControl event is as follows for all controls but the MultiPage control:

Private Sub object_RemoveControl(ByVal Control As MSForms.Control)

Here, object is a valid object, and Control is a valid control.

The syntax for the RemoveControl event is as follows for the MultiPage control:

Private Sub multipage_RemoveControl(ByVal Index As Long, ByVal Control As MSForms.Control)

Here, multipage is a valid MultiPage object. For a Multipage control, Index specifies the Page object in the MultiPage control that contains the control to be deleted.

The Click event applies to the CheckBox, ComboBox, CommandButton, Frame, Image, Label, ListBox, MultiPage, OptionButton, TabStrip, and ToggleButton controls. It doesn't apply to the TextBox, ScrollBar, or SpinButton controls, but it does apply to the UserForm object.

The Click event occurs when the user clicks a control with the primary mouse button and also when the user selects a value for a control that has more than one possible value. For most controls, this means that each time the user clicks the control, the event fires. But there are a few exceptions:

For all controls except the TabStrip control and the MultiPage control, the Click event needs no arguments, as follows:

Private Sub object_Click()

For a TabStrip control or a MultiPage control, your code must react to the Index argument, a required Long (data type) argument that VBA passes to indicate the affected tab or page of the control:

Private Sub object_Click(ByVal Index As Long)

Here, object is a valid MultiPage control or TabStrip control.

The Change event applies to the CheckBox, ComboBox, ListBox, MultiPage, OptionButton, ScrollBar, SpinButton, TabStrip, TextBox, and ToggleButton controls. This event fires when the Value property of a control changes. This change can occur either through an action of the user's (such as typing text into a text box, selecting an option button, selecting or clearing a check box, clicking a toggle button, or changing the page displayed on a multipage control) or through an action taken programmatically at runtime.

Bear in mind that when the Change event is fired by an action of the user's, that action may also trigger a Click event. (Even when this happens, Change is regarded as a better way of determining the new Value of the control than Click—though for many purposes Click will work satisfactorily as well.) Changing the Value property of a control manually at design time doesn't fire a Change event.

The syntax for the Change event is as follows:

Private Sub object_Change()

The Change event is useful for updating other controls after the user changes a control. For example, if the user enters the name for a new report into a text box (here, txtReportName), you could use the Change event to automatically insert into another text box (here called txtFileName) the name of the file in which to save the report:

Private Sub txtReportName_Change()
    txtFileName.Text = txtReportName.Text & ".txt"
End Sub

The Enter and Exit events apply to CheckBox, ComboBox, CommandButton, Frame, ListBox, MultiPage, OptionButton, ScrollBar, SpinButton, TabStrip, TextBox, and ToggleButton controls.

The Enter event fires when the focus is moved from one control on a user form to another control. The event fires just before the second control receives the focus.

Like the Enter event, the Exit event fires when the focus is moved from one control on a user form to another control. However, the Exit event fires just before the first event loses the focus.

The syntax for the Enter event is as follows:

Private Sub object_Enter()

The syntax for the Exit event is a little more complex:

Private Sub object_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Here, Cancel is a required argument specifying event status. The default setting is False, which specifies that the control involved should handle the event and that the focus will pass to the next control; a setting of True specifies that the application handle the event, which keeps the focus on the current control.

By using the Enter and Exit events, you can track the user's progress through the controls on a user form.

The Exit event is useful for checking to see if the user has made an appropriate selection in the control or has entered a suitable value. For example, you could check the user's entry in the control and, if you find it inappropriate, display a message box alerting the user to the problem and then return the focus to the control so that the user can try again.

The BeforeUpdate event applies to the CheckBox, ComboBox, ListBox, OptionButton, ScrollBar, SpinButton, TextBox, and ToggleButton controls. This event occurs as the value or data in the specified control is changed; you can use the event to evaluate the change and decide whether to implement it.

The syntax for the BeforeUpdate event is as follows:

Private Sub object_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

Here, object is a valid object, and Cancel is a required argument indicating the status of the event. The default setting of False makes the control handle the event; True prevents the update from being executed and makes the application handle the event.

Here's the sequence in which events fire as you move to a control, update it, and move on:

The AfterUpdate event applies to the CheckBox, ComboBox, ListBox, OptionButton, ScrollBar, SpinButton, TextBox, and ToggleButton controls. This event fires after the user changes information in a control and after that update has been executed.

The syntax for the AfterUpdate event is the same for all the controls and objects it applies to:

Private Sub object_AfterUpdate()

The KeyDown event and KeyUp event apply to the CheckBox, ComboBox, CommandButton, Frame, ListBox, MultiPage, OptionButton, ScrollBar, SpinButton, TabStrip, TextBox, and ToggleButton controls and to the UserForm object. These events don't apply to the Image and Label controls.

The KeyDown event fires when the user presses a key on the keyboard. The KeyUp event fires when the user releases the key. The KeyDown and KeyUp events also occur when a key is sent to the user form or control programmatically by using the SendKeys statement. These events don't occur when the user presses Enter when the user form contains a CommandButton control with its Default property set to True, nor when the user presses Esc when the user form contains a CommandButton control with its Cancel property set to True.

When the keystroke moves the focus to another control, the KeyDown event fires for the original control, while the KeyPress and KeyDown events fire for the control to which the focus is moved.

The KeyPress event fires after the KeyDown event and before the KeyUp event.

The syntax for the KeyDown event is as follows:

Private Sub object_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As
Integer)

The syntax for the KeyUp event is as follows:

Private Sub object_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As
Integer)

Here, object is an object name and is required. KeyCode is a required Integer argument specifying the key code of the key pressed. For example, the key code for the letter t is 84. The key code isn't an ANSI value—it's a special number that identifies the key on the keyboard.

Shift is a required argument specifying whether the Shift, Ctrl, or Alt key was pressed. Use the constants or values shown in Table 15.7.

The KeyPress event applies to the CheckBox, ComboBox, CommandButton, Frame, ListBox, MultiPage, OptionButton, ScrollBar, SpinButton, TabStrip, TextBox, and ToggleButton controls. It also applies to the UserForm object. It doesn't apply to the Label control.

The KeyPress event fires when the user presses a printable character, Ctrl plus an alphabetic character, Ctrl plus a special character (symbols), the Esc key, or the Backspace key while the control or object in question has the focus. Pressing the Tab key, the Enter key, or an arrow key doesn't cause the KeyPress event to fire, nor does a keystroke that moves the focus to another control from the current control.

Technically, only ANSI keys fire the KeyPress event. The Delete key isn't an ANSI key, so pressing the Delete key to delete, say, text in a text box doesn't fire the KeyPress event. But deleting the same text in the same text box using the Backspace key does because Backspace is an ANSI key.

The KeyPress event fires after the KeyDown event and before the KeyUp event. It also fires when you use SendKeys to send keystrokes to a user form programmatically.

The syntax for the KeyPress event is as follows:

Private Sub object_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Here, object is a required argument specifying a valid object, and KeyAscii is a required Integer argument specifying an ANSI key code. To get the ANSI key code, use the Asc function. For example, Asc("t") returns the ANSI key code for the letter t (the code is 116).

By default, the KeyPress event processes the code for the key pressed—in humble terms, what you press is what you get. For example, if you press the t key, you get a t; if you press the Delete key, you get a Delete action; and so on. By using a KeyPress event procedure, you can perform checks such as filtering out all nonnumeric keys when the user must enter a numeric value.

The MouseDown and MouseUp events apply to the CheckBox, ComboBox, CommandButton, Frame, Image, Label, ListBox, MultiPage, OptionButton, ScrollBar, SpinButton, TabStrip, TextBox, and ToggleButton controls and to the UserForm object. The MouseDown event fires when the user presses a button on the mouse, and the MouseUp event occurs when the user releases that button. The Click event fires after the MouseUp event occurs.

The syntax for the MouseDown and MouseUp events is as follows for all controls except for MultiPage and TabStrip:

Private Sub object_MouseDown(ByVal Button As Integer, ByVal Shift As Integer,
ByVal X As Single, ByVal Y As Single)
Private Sub object_MouseUp(ByVal Button As Integer, ByVal Shift As Integer,
ByVal X As Single, ByVal Y As Single)

The syntax for the MouseDown and MouseUp events with the MultiPage and TabStrip controls adds an Index argument to specify the index of the page or the tab involved:

Private Sub object_MouseUp(ByVal Index As Long, ByVal Button As Integer, ByVal
Shift As Integer, ByVal X As Single, ByVal Y As Single)
Private Sub object_MouseDown(ByVal Index As Long, ByVal Button As Integer, ByVal
Shift As Integer, ByVal X As Single, ByVal Y As Single)

Here, object is a valid object for the statement.

Index returns −1 if the user clicks outside the page or tab area of the control but still within the control (for example, to the right of the rightmost tab in a top-tab tab strip).

Button is a required Integer argument specifying the mouse button that triggered the event. Table 15.8 lists the possible values for Button.

Shift is a required argument specifying whether the Shift, Ctrl, or Alt key was pressed. Table 15.9 lists the values for Shift.

You can also detect a single key by using the key masks listed in Table 15.7.

X is a required Single argument specifying the horizontal position in points from the left edge of the user form, frame, or page. Y is a required Single argument specifying the vertical position in points from the top edge of the user form, frame, or page.

The MouseMove event is available to the CheckBox, ComboBox, CommandButton, Frame, Image, Label, ListBox, MultiPage, OptionButton, TabStrip, TextBox, and ToggleButton controls and to the UserForm object. This event fires when the user moves the mouse pointer over the control or object in question.

The syntax for the MouseMove event is different for the MultiPage control and the TabStrip control than for the other controls and for the UserForm object. The syntax for the other controls is as follows:

Private Sub object_MouseMove(ByVal Button As Integer, ByVal Shift As Integer,
ByVal X As Single, ByVal Y As Single)

The syntax for the MultiPage control and the TabStrip control is as follows:

Private Sub object_MouseMove(ByVal Index As Long, ByVal Button As Integer,
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

Here, object is a required argument specifying a valid object.

For the MultiPage and TabStrip controls, Index is a required argument that returns the index of the Page object in the MultiPage control or the Tab object in the TabStrip control associated with the event procedure.

Button is a required Integer argument that returns which mouse button (if any) the user is pressing. Table 15.10 lists the values for Button.

Shift is a required Integer argument that returns a value indicating whether the user is pressing the Shift, Alt, and/or Ctrl keys. Refer back to Table 15.9 for the list of Shift values.

X is a required Single argument that returns a value specifying the horizontal position in points from the left edge of the user form, frame, or page. Y is a required Single argument specifying the vertical position in points from the top edge of the user form, frame, or page.

As with the MouseDown and MouseUp events, you can also detect a single key by using the key masks listed in Table 15.7.

Like windows, user forms largely experience life as a nonstop sequence of mouse events. MouseMove events monitor where the mouse pointer is on the screen and which control has captured it. MouseMove events fire even if you use the keyboard to move a user form from under the mouse pointer because the mouse pointer ends up in a different place in relation to the user form even though it hasn't moved in the conventional sense.

One use for the MouseMove event is to display appropriate text or an image for a control at which the user is pointing. For example, suppose a user form provides a list of available products, with each product's title appearing in a label. When the user positions the mouse pointer over a title in the label, you could use the MouseMove event to load a picture of the product into an Image control and a short description into another label.

The BeforeDragOver event applies to the UserForm object itself and to the following controls: CheckBox, ComboBox, CommandButton, Frame, Image, Label, ListBox, MultiPage, OptionButton, ScrollBar, SpinButton, TabStrip, TextBox, and ToggleButton. The BeforeDragOver event occurs when the user is performing a drag-and-drop operation.

The syntax for the BeforeDragOver event depends on the object or control in question. The basic syntax for the UserForm object and all controls except the Frame, TabStrip, and MultiPage is as follows, where object is a valid UserForm or control:

Private Sub object_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, ByVal
Control As MSForms.Control, ByVal Data As MSForms.DataObject, ByVal X As Single,
ByVal Y As Single, ByVal State As MSForms.fmDragState, ByVal Effect As MSForms.
ReturnEffect, ByVal Shift As Integer)

The syntax for the BeforeDragOver event with the Frame control is as follows, where frame is a valid Frame control:

Private Sub frame_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, ByVal
Control As MSForms.Control, ByVal Data As MSForms.DataObject, ByVal X As Single,
ByVal Y As Single, ByVal State As MSForms.fmDragState, ByVal Effect As MSForms.
ReturnEffect, ByVal Shift As Integer)

The syntax for the BeforeDragOver event with the MultiPage control is as follows, where multipage is a valid MultiPage control:

Private Sub multipage_BeforeDragOver(ByVal Index As Long, ByVal Cancel As MSForms.
ReturnBoolean, ByVal Control As MSForms.Control, ByVal Data As MSForms.
DataObject, ByVal X As Single, ByVal Y As Single, ByVal State As MSForms.
fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)

The syntax for the BeforeDragOver event with the TabStrip control is as follows, where tabstrip is a valid TabStrip control:

Private Sub tabstrip_BeforeDragOver(ByVal Index As Long, ByVal Cancel As MSForms.
ReturnBoolean, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As
Single, ByVal DragState As MSForms.fmDragState, ByVal Effect As MSForms.
ReturnEffect, ByVal Shift As Integer)

These are the different parts of the statements:

You use the BeforeDragOver event to control drag-and-drop actions that the user performs. Use the DragState argument to make sure that the mouse pointer is within range of a target.

The BeforeDropOrPaste event applies to the CheckBox, ComboBox, CommandButton, Frame, Image, Label, ListBox, MultiPage, OptionButton, ScrollBar, SpinButton, TabStrip, TextBox, and ToggleButton controls and to the UserForm object.

The BeforeDropOrPaste event occurs just before the user drops or pastes data onto an object.

The syntax for the BeforeDropOrPaste event is different for the MultiPage and TabStrip controls than for the UserForm object and for the other controls. The basic syntax is as follows:

Private Sub object_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, ByVal
Control As MSForms.Control, ByVal Action As MSForms.fmAction, ByVal Data As
MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal Effect As
MSForms.ReturnEffect, ByVal Shift As Integer)

The syntax for the MultiPage control is as follows, where multipage is a valid MultiPage control:

Private Sub multipage_BeforeDropOrPaste(ByVal Index As Long, ByVal Cancel  As
MSForms.ReturnBoolean, ByVal Control As MSForms.Control, ByVal Action As MSForms.
fmAction, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As
Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)

The syntax for the TabStrip control is as follows, where tabstrip is a valid TabStrip control:

Private Sub tabstrip_BeforeDropOrPaste(ByVal Index As Long, ByVal Cancel  As
MSForms.ReturnBoolean, ByVal Action As MSForms.fmAction, ByVal Data As MSForms.
DataObject, ByVal X As Single, ByVal Y As Single, ByVal Effect  As MSForms.
ReturnEffect, ByVal Shift As Integer)

Here are the parts of the syntax:

The BeforeDropOrPaste event fires when a data object is transferred to a MultiPage or TabStrip and just before the drop or paste operation occurs on other controls.

The DblClick event applies to the CheckBox, ComboBox, CommandButton, Frame, Image, Label, ListBox, MultiPage, OptionButton, TabStrip, TextBox, and ToggleButton controls. It also applies to the UserForm object. The DblClick event occurs when the user double-clicks a control or object with the primary mouse button. The double-click must be fast enough to register as a double-click in Windows (this speed is controlled by the setting on the Buttons tab in the Mouse Properties dialog box in Control Panel) and occurs after the MouseDown event, the MouseUp event, and the Click event (for controls that support the Click event).

The DblClick event takes different syntax for the MultiPage and TabStrip controls than for the other controls and for the user form. For the MultiPage and TabStrip controls, the syntax is as follows:

Private Sub object_DblClick(ByVal Index As Long, ByVal Cancel As MSForms
.ReturnBoolean)

The syntax for the DblClick event for other controls is as follows:

Private Sub object_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Here, object is a required argument specifying a valid object. For the MultiPage control and the TabStrip control, Index is a required argument specifying the Page object within a MultiPage control or the Tab object within a TabStrip control to be associated with the event procedure.

Cancel is a required argument specifying the status of the event. The default setting of False causes the control to handle the event; True causes the application to handle the event instead and causes the control to ignore the second click.

In controls that support both the Click event and the DblClick event, the Click event occurs before the DblClick event. If you take an interface action (such as displaying a message box) with the Click event procedure, it blocks the DblClick event procedure from running. In the following example, the DblClick event procedure doesn't run:

Private Sub CommandButton1_Click()
    MsgBox "Click event"
End Sub

Private Sub CommandButton1_DblClick _
    (ByVal Cancel As MSForms.ReturnBoolean)
    MsgBox "Double-click event"
End Sub

However, you can execute noninterface statements in the Click event procedure without blocking the DblClick event procedure. The following example declares a private String variable named strMessage in the declarations portion of the code sheet for the user form. The Click event procedure for the CommandButton1 command button assigns text to strMessage. The DblClick event procedure assigns more text to strMess and then displays a message box containing strMessage so that you can see that both events have fired. Don't step into this code by pressing F8 in the VBA Editor—instead, press F5 to run it, or it won't work:

Private strMess As String
Private Sub CommandButton1_Click()
    strMess = "Click event" & vbCr
End Sub

Private Sub CommandButton1_DblClick _
    (ByVal Cancel As MSForms.ReturnBoolean)
    strMessage = strMessage & "Double-click event"
    MsgBox strMessage
End Sub

For most controls you won't want to use both a Click event procedure and a DblClick event procedure—you'll choose one or the other as appropriate to the control's purpose.

The Error event applies to the CheckBox, ComboBox, CommandButton, Frame, Image, Label, ListBox, MultiPage, OptionButton, ScrollBar, SpinButton, TabStrip, TextBox, and ToggleButton controls. It also applies to the UserForm object. The Error event fires when a control encounters an error and is unable to return information about the error to the program that called the control.

The syntax for the Error event for the UserForm object and for all controls except the MultiPage control is as follows:

Private Sub object_Error(ByVal Number As Integer, ByVal Description As MSForms.
ReturnString, ByVal SCode As Long, ByVal Source As String, ByVal HelpFile As
String, ByVal HelpContext As Long, ByVal CancelDisplay As MSForms.ReturnBoolean)

The syntax for the Error event for the MultiPage control is as follows, where multipage is a valid MultiPage control:

Private Sub multipage_Error(ByVal Index As Long, ByVal Number As Integer, ByVal
Description As MSForms.ReturnString, ByVal SCode As Long, ByVal Source As String,
ByVal HelpFile As String, ByVal HelpContext As Long, ByVal CancelDisplay As
MSForms.ReturnBoolean)

These are the components of the syntax:

The DropButtonClick event fires when the user displays or hides a drop-down list on a ComboBox by clicking the drop-down button or by pressing the F4 key. DropButtonClick also fires when the user press the F4 key with a TextBox control selected, though this manifestation of the event is arcane enough to be singularly useless. It also fires when the DropDown method is executed in VBA to display the drop-down list, and it fires again when the DropDown method is executed again to hide the drop-down list.

The syntax for the DropButtonClick event is as follows

Private Sub object_DropButtonClick( )

Here, object is a valid ComboBox or TextBox control.

One use for the DropButtonClick event is to add items to a ComboBox control rather than adding them at load time via the Initialize event. By adding these items only on demand (I'm assuming the user might not use the ComboBox control at all or might type information into its text box area), you can cut down on load time for the user form. You can also load the ComboBox with data relevant to the other choices the user has made in the dialog box, allowing for more targeted information than you could have provided by loading the ComboBox with the Initialize event.

The SpinDown and SpinUp events apply only to the SpinButton control. SpinDown and SpinUp are used to control what happens when the user clicks either the down-arrow button and up-arrow button, respectively, of a vertical SpinButton control or the right-arrow button and left-arrow button, respectively, of a horizontal SpinButton control. The SpinDown event fires when the user clicks the down-arrow or right-arrow button, and the SpinUp event fires when the user clicks the up-arrow or left-arrow button.

The syntax for the SpinUp event and the SpinDown event is

Private Sub spinbutton_SpinDown()
Private Sub spinbutton_SpinUp()

Here, spinbutton is a SpinButton control.

By default, the SpinDown event decreases and the SpinUp event increases the Value property of the SpinButton by the SmallChange increment.