USING MULTIPAGE DIALOG BOXES

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 two-page MultiPage control with tabs that 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:

• To add a page, right-click the label and choose New Page from the context menu. VBA will add a new page of the default size and will name it Pagen, where n is the next number after the current number of pages (even if the other pages have names other than Page1, Page2, and so on).
• To rename a page in a MultiPage control, right-click the label and choose Rename from the context menu. In the Rename dialog box (see Figure 15.5), enter the caption (the label text) for the page in the Caption text box, the accelerator key in the Accelerator Key text box, and any control-tip text (the tip users see when they move the mouse pointer over the tab for the page) in the Control Tip Text text box. Click the OK button to close the Rename dialog box.

  

• To delete a page from a MultiPage control, right-click the label and choose Delete Page from the context menu. The VBA Editor will remove the page without prompting for confirmation.
• To move a page to a different place in the MultiPage control, right-click the label and choose Move from the context menu to display the Page Order dialog box (see Figure 15.6). In the Page Order list box, select the page or pages that you want to move (Shift+click to select multiple contiguous pages, Ctrl+click to select multiple, noncontiguous pages), and then use the Move Up and Move Down buttons to rearrange the page or pages as desired. When you've finished, click the OK button to close the Page Order dialog box.

  

• To specify which page of a multipage dialog box to display by default, use the Value property of the MultiPage control. You can set this property either at design time or at runtime. For example, you could use an initialization procedure such as the one shown here to display the third page (identified by the value 2, because the page numbering starts at 0) of a dialog box with a MultiPage control called MyMulti at runtime:

          Sub UserForm_Initialize()
            MyMulti.Value = 2
          End Sub

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 in the entire form (not just within the page on which it appears).

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

• What's the best way to divide the information or options in the dialog box? What belongs on which page? Which information or options will the user expect to find grouped together?
• Which controls should appear on each page? Most dialog boxes need at least a pair of command buttons—such as OK and Cancel (or End or Close)—available from each page to allow users to dismiss the dialog box from whichever page they happen to end up on. In rare instances, you may want to force users to return to a particular page in order to close a dialog box. In these cases, make sure each page that doesn't contain a command button to dismiss the dialog box tells the users where they will find such a command button.
• For settings, do you need to have an Apply button (as well as an OK button) to apply the changes on a particular page without closing the dialog box?

Because each control in a multipage form 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:

• The Style property offers fmStyleTabs (the default setting, showing tabs for navigating between the pages), fmStyleButtons (which gives each page a rectangular button, with the button for the current page appearing pushed in), or fmStyleNone (which provides no means of navigating between the pages and no indication of the borders of the multipage dialog box). fmStyleNone can be useful for creating user forms that have two or more alternative layouts of which the user will only ever need to see one at a time.

  By including one set of controls on one page of the multipage dialog box and another set of controls on another page, you can present two seemingly different dialog boxes by doing nothing more than changing which page of the MultiPage control is displayed. For example, you can use this technique to create a “wizard” that guides users through a multistep process. Just add a Next button that they click to move to each subsequent step.

• The TabOrientation property controls where the tabs (or buttons) for the pages appear on the control. Your choices are fmTabOrientationTop (the default setting, placing the tabs at the top of the control), fmTabOrientationBottom, fmTabOrientationLeft, and fmTabOrientationRight.

  Experiment with the effects that the bottom, left, and right orientations offer, but unless they provide significant advantages over the more normal top orientation, use them sparingly—if at all. Users won't thank you for deviating from the traditional, familiar interface unnecessarily.

• The MultiRow property controls whether a MultiPage control has one row of tabs for its pages (False) or multiple rows (True). When you have MultiRow set to True, the VBA Editor adds the second or subsequent rows of tabs when you run out of space on the first or current row.

The MultiPage control doesn't have to take up the whole dialog box—in fact, most dialog boxes keep the key command buttons such as OK and Cancel 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 users may miss the tabs, particularly if they're just skimming the controls looking for a particular option.

USING THE TABSTRIP CONTROL

Forms that use a TabStrip are substantially different from multipage dialog boxes. A TabStrip control can be used not to rearrange other controls, but to just change the data that appears in them as the user moves from one set of data to another. In other words, the layout of the controls remains static. Only the values displayed in the controls change from page to page on the strip.

For instance, you might use a dialog box driven by a TabStrip to view and update the records in a data source such as a Word table, an Excel spreadsheet, or an Access database. This next 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 TabStrip.

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 TabStrip should appear at the top, bottom, left, or right of its control. But be sure to have a pretty good reason if you're departing from convention. The top position is expected and makes the most sense.

The TabStrip can contain zero, one, or more tabs. For most purposes, there's little point in having only one tab on a TabStrip, and even less in having no tab at all. But if you dynamically populate the TabStrip 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 TabStrip 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, do so now.

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

After specifying the worksheet, the code in Listing 15.2 essentially repeats itself for each of the text boxes that appear 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 holds 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:

• Line 1 declares the procedure tabSurfer_Change, which executes automatically whenever the Change event of the tabSurfer TabStrip fires. The Change event fires each time the user clicks a new tab, so you use this event to control the information displayed in the text boxes.
• The Change event also fires when a tab is added to (or removed from) the TabStrip. Because the DataSurfer user form uses the Initialize event procedure to populate the TabStrip with tabs (one per worksheet in the workbook), you do need to prevent the Change event procedure from running unnecessarily during the initialization phase of your program. So, the user form declares a private Boolean variable named blnInitializing that the Initialize procedure sets to True while it's running and to False just before it ends.
• Line 2 of the Change event procedure checks to make sure that blnInitializing is False. If it's not, the Initialize procedure has fired the event, and the Change procedure does not need to load the information into the cells—so execution continues at line 21, just before the end of the procedure. But once the Initialize procedure has finished running, blnInitializing will be set to False, and the Change event procedure will run each time the user changes tabs in the TabStrip.
• Line 3 begins a With statement that works with the appropriate worksheet in the active workbook: ActiveWorkbook.Sheets(tabSurfer.Value + 1). The Value property of the tabSurfer TabStrip tells us which tab in the TabStrip is selected. Because the first tab in the TabStrip is numbered 0 and the first worksheet in the workbook is numbered 1, you need to add 1 to the Value of the TabStrip to even the numbers.
• Line 4 is a comment. Line 5 uses the Activate method to activate the worksheet in question.
• Lines 6 through 19 then set the Text property of each text box in the user form to the contents of the corresponding cell in the second column on the worksheet. For example, line 6 sets the Text property of the txtFirstName text box (which appears under the First Name label in the dialog box) to the contents of the first cell in the second column: .Cells(1, 2).Text.
• Line 20 ends the With statement, line 21 ends the If statement, and line 22 ends the procedure.

USING PICTURES IN FORMS

VBA includes extensive graphics capabilities that allow you to make your forms look pretty much any way you want them to. This book doesn't go into design aesthetics in any detail, but there's much you can do to make your forms attractive. You can fiddle with Format ➢ Order to pile controls on top of each other. Controls like the command button have their own Picture properties, as do forms themselves. Take a look at Figure 15.9. It shows an image control displaying a photo, a background texture (loaded into the form's Picture property), and a command button that blends into the background (because its BackStyle property is set to Transparent).

To use 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.

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

• If necessary, set the alignment of the picture by using the PictureAlignment property. (If the picture fully fills the Image control—neither overlapping it nor leaving parts of it empty—you may not need to set the alignment for it.) Table 15.1 shows the constants and values for the PictureAlignment property.

  CONSTANT	VALUE	PICTURE ALIGNMENT IN IMAGE CONTROL
  fmPictureAlignmentTopLeft	0	Top left
  fmPictureAlignmentTopRight	1	Top right
  fmPictureAlignmentCenter	2	Centered
  fmPictureAlignmentBottomLeft	3	Bottom left
  fmPictureAlignmentBottomRight	4	Bottom right

• If necessary, clip, stretch, or zoom the picture by using the PictureSizeMode property: fmPictureSizeModeClip (0) clips the picture to fit the Image control. FmPictureSizeModeStretch (1) stretches or squeezes the picture so that it fits the Image control (this option often makes for strange effects). And fmPictureSizeModeZoom (2) enlarges or reduces the picture so that its nearest dimension exactly fits the width or height of the Image control without changing the picture's proportions (this option usually leaves an unfilled gap on the other side).
• If you need to tile the image to take up the remaining space in the control, set the PictureTiling property to True. This option is rarely used with database work.
• If you need to adjust the position of the picture relative to its caption, set the PicturePosition property of the check box, command button, label, option button, or toggle button in question. Table 15.2 shows the constants and values for PicturePosition.

  CONSTANT	VALUE	PICTURE POSITION	CAPTION ALIGNMENT
  fmPicturePositionLeftTop	0	Left of the caption	With top of picture
  fmPicturePositionLeftCenter	1	Left of the caption	Centered on picture
  fmPicturePositionLeftBottom	2	Left of the caption	With bottom of picture
  fmPicturePositionRightTop	3	Right of the caption	With top of picture
  fmPicturePositionRightCenter	4	Right of the caption	Centered on picture
  fmPicturePositionRightBottom	5	Right of the caption	With bottom of picture
  fmPicturePositionAboveLeft	6	Above the caption	With left edge of picture
  fmPicturePositionAboveCenter	7	Above the caption	Centered below picture (the default setting)
  fmPicturePositionAboveRight	8	Above the caption	With right edge of picture
  fmPicturePositionBelowLeft	9	Below the caption	With left edge of picture
  fmPicturePositionBelowCenter	10	Below the caption	Centered above picture
  fmPicturePositionBelowRight	11	Below the caption	With right edge of picture
  fmPicturePositionCenter	12	In center of control	Centered horizontally and vertically on top of picture

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, you could display two graphics illustrating a choice of two formats for a document. Then users could click the appropriate picture to signal their choice.

INITIALIZE EVENT

An 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. Then 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.

QUERYCLOSE EVENT

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. Say that you have established that users had entered a lot of data in a 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 illustrated 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.

ACTIVATE EVENT

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 first 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()

DEACTIVATE EVENT

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.

RESIZE EVENT

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 respond to a resized form. For example, you might resize a text box so that it occupies most of the width of the user form it resides on (see Figure 15.10) by using code such as that shown in Listing 15.3.

The code works like this:

• 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. The Me keyword always refers to the current form, the form in which the Me command is located. So it's easy to understand that the code Unload Me simply tells the form to close itself.
• 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 (repeatedly pressing F8) in 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.

TERMINATE EVENT

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()

SCROLL EVENT

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:

• Index A required argument specifying the page of the MultiPage with which the event procedure is to be associated.
• ActionX and ActionY Required arguments determining the user's horizontal and vertical actions (respectively), as shown in Table 15.6.

  CONSTANT	VALUE	SCROLL BOX MOVEMENT
  fmScrollActionNoChange	0	There was no change or movement.
  fmScrollActionLineUp	1	The user moved the scroll box a short way upward on a vertical scroll bar (equivalent to pressing the ↑ key) or a short way to the left on a horizontal scroll bar (equivalent to pressing the ← key).
  fmScrollActionLineDown	2	The user moved the scroll box a short way downward on a vertical scroll bar (equivalent to pressing the ↓ key) or a short way to the right on a horizontal scroll bar (equivalent to pressing the → key).
  fmScrollActionPageUp	3	The user moved the scroll box up one page on a vertical scroll bar (equivalent to pressing the Page Up key) or one page to the left on a horizontal scroll bar (also equivalent to pressing the Page Up key).
  fmScrollActionPageDown	4	The user moved the scroll box down one page on a vertical scroll bar (equivalent to pressing the Page Down key) or one page to the right on a horizontal scroll bar (also equivalent to pressing the Page Down key).
  fmScrollActionBegin	5	The user moved the scroll box to the top of a vertical scroll bar or to the left end of a horizontal scroll bar.
  fmScrollActionEnd	6	The user moved the scroll box to the bottom of a vertical scroll bar or to the right end of a horizontal scroll bar.
  fmScrollAction PropertyChange	8	The user moved the scroll box, changing the value of either the ScrollTop property or the ScrollLeft property.
  fmScrollActionControl Request	9	The scroll action was requested by a control in the container in question.
  fmScrollActionFocusRequest	10	The user moved the focus to a different control. This movement scrolls the user form if necessary so that the selected control is fully displayed in the available area.

• RequestDx The distance to move the scroll box horizontally, specified in points.
• RequestDy The distance to move the scroll box vertically, specified in points.
• ActualDx The distance the scroll box moved horizontally, measured in points.
• ActualDy The distance the scroll box moved vertically, measured in points.

ZOOM 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, and 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 changes at runtime. The Zoom property can be changed either automatically through code or by the user's manipulating—dragging a scroll bar's thumb, for example—a control that changes the property because you've written code that responds this way.

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

LAYOUT EVENT

A Layout event is triggered 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.

ADDCONTROL EVENT

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

REMOVECONTROL EVENT

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.

CLICK EVENT

The most common event of all in VBA, the Click event services the CheckBox, ComboBox, CommandButton, Frame, Image, Label, ListBox, MultiPage, OptionButton, TabStrip, and ToggleButton controls. It is not available to the TextBox, ScrollBar, or SpinButton controls, but it is a member of the UserForm object.

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

• Clicking a disabled control fires the Click event of the user form (as if the user were clicking the user form through the control).
• The Click event of an OptionButton control fires when the user clicks the option button to select it. If the option button is already selected, clicking it has no effect. (On the other hand, the Click event of a CheckBox control fires each time the user clicks the check box—either to select it or to clear it.)
• The Click event of a ListBox control or ComboBox control fires when the user clicks to select an item from the list (not when the user clicks on the drop-down arrow or in the undropped portion of the combo box). If the user clicks an already-selected item, the Click event doesn't fire again.
• The Click event of a ToggleButton control occurs whenever the toggle button is clicked and when its Value property is changed. This means that it isn't a good idea to use the Click event of the ToggleButton control to toggle its Value.
• The Click event of a selected CommandButton control fires when you press the spacebar.
• The Click event of the default command button (the button with its Default property set to True) fires when the user presses Enter with no other command button selected.
• The Click event of the command button with its Cancel property set to True fires when the user presses Esc. The Click event for a control with an accelerator key set also fires when the user presses the accelerator key.

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.

CHANGE EVENT

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—although 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

ENTER AND EXIT EVENTS

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.

BEFOREUPDATE EVENT

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 focus to a control, update it, and move on:

1. The Enter event for the control fires when you move the focus to the control.
2. The BeforeUpdate event for the control fires after you've entered the information for the update (for example, after you've pressed a key in a text box) but before the update is executed. By setting Cancel to True, you can prevent the update from taking place. (If you don't set Cancel to True, the update occurs and the AfterUpdate event can't prevent it from occurring.)
3. The AfterUpdate event for the control fires after you've entered the information in the control and the update has been executed. If you set the Cancel argument for BeforeUpdate to True, the AfterUpdate event doesn't fire.
4. The Exit event for the control fires when you move from this control to another control. (After the Exit event fires for the control you've left, the Enter event fires for the control to which you have moved the focus.)

AFTERUPDATE EVENT

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()

KEYDOWN AND KEYUP EVENTS

The KeyDown event and KeyUp event work with the CheckBox, ComboBox, CommandButton, Frame, ListBox, MultiPage, OptionButton, ScrollBar, SpinButton, TabStrip, TextBox, and ToggleButton controls and to the UserForm object. These events are not available 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.

KEYPRESS EVENT

The KeyPress event is a member of the CheckBox, ComboBox, CommandButton, Frame, ListBox, MultiPage, OptionButton, ScrollBar, SpinButton, TabStrip, TextBox, and ToggleButton controls. It also is a member of the UserForm object. The Label control has no KeyPress event.

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—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.

MOUSEDOWN EVENT AND MOUSEUP EVENT

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 a MouseUp event occurs when the user releases that button. A Click event fires after a 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 TabStrip).

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.

MOUSEMOVE EVENT

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 most windows in the Windows operating system, user forms largely experience life as a nonstop sequence of various 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. Moving the mouse pointer onto an object without clicking it has become known as hovering. It is common in websites and some applications to respond to hovering by displaying a tip identifying the object hovered on. If you hover your mouse on an Office application's Ribbon, for example, a tooltip pops out.

BEFOREDRAGOVER EVENT

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. A BeforeDragOver event is triggered 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:

• Index is the index of the Page object in a MultiPage control (or the Tab object in a TabStrip control) that is affected by the drag-and-drop.
• Cancel is a required argument giving the status of the BeforeDragOver event. The default setting is False, which makes the control handle the event. A setting of True makes the application handle the event.
• Control is a required argument specifying the control that is being dragged over.
• Data is a required argument specifying the data being dragged.
• X is a required argument specifying the horizontal distance in points from the left edge of the control. Y is a required argument specifying the vertical distance in points from the top of the control.
• DragState is a required argument specifying where the mouse pointer is in relation to a target (a location at which the data can be dropped). Table 15.11 lists the constants and values for DragState.

  CONSTANT	VALUE	POSITION OF MOUSE POINTER
  fmDragStateEnter	0	Within range of a target
  fmDragStateLeave	1	Outside the range of a target
  fmDragStateOver	2	At a new position, but remains within range of the same target

• Effect is a required argument specifying the operations the source of the drop is to support, as listed in Table 15.12.

  CONSTANT	VALUE	DROP EFFECT
  fmDropEffectNone	0	Doesn't copy or move the source to the target
  fmDropEffectCopy	1	Copies the source to the target
  fmDropEffectMove	2	Moves the source to the target
  fmDropEffectCopyOrMove	3	Copies or moves the source to the target

• Shift is a required argument specifying whether the Shift, Ctrl, or Alt key is held down during the drag-and-drop operation, as listed in Table 15.7.

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.

BEFOREDROPORPASTE EVENT

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.

A BeforeDropOrPaste event fires 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:

• object is a required object specifying a valid object.
• For the MultiPage control, Index is a required argument specifying the Page object involved.
• Cancel is a required argument giving the status of the event. The default setting of False makes the control handle the event; True makes the application handle the event.
• Control is a required argument specifying the target control.
• Action is a required argument specifying the result of the drag-and-drop operation. Table 15.13 shows the constants and values for Action.

  CONSTANT	VALUE	ACTION TAKEN
  fmActionPaste	2	Pastes the object into the target.
  fmActionDragDrop	3	The user has dragged the object from its source and dropped it on the target.

• Data is a required argument specifying the data (contained in a DataObject) being dragged and dropped.
• X is a required argument specifying the horizontal distance in points from the left edge of the control for the drop. Y is a required argument specifying the vertical distance in points from the top of the control.
• Effect is a required argument specifying whether the drag-and-drop operation copies the data or moves it, as listed in Table 15.12.
• Shift is a required argument specifying whether the user has pressed the Shift, Ctrl, and/or Alt keys, as listed in Table 15.7.

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

DBLCLICK EVENT

The DblClick event works with the CheckBox, ComboBox, CommandButton, Frame, Image, Label, ListBox, MultiPage, OptionButton, TabStrip, TextBox, and ToggleButton controls, as well as the UserForm object.

A DblClick event occurs when the user double-clicks a control or object with the left 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 has a different syntax for the MultiPage and TabStrip controls than for the other controls or 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 non-interface 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.

ERROR EVENT

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. We will explore error handling in depth in Chapter 17, “Debugging Your Code and Handling Errors.”

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:

• object is the name of a valid object.
• For a MultiPage control, Index is the index of the Page object in the MultiPage control associated with the event.
• Number is a required argument that returns the value used by the control to identify the error.
• Description is a required String argument describing the error.
• SCode is a required argument giving the OLE (Object Linking and Embedding) status code for the error.
• Source is a required String argument containing the string identifying the control involved.
• HelpFile is a required String argument containing the full path to the Help file that contains the Description.
• HelpContext is a required Long argument containing the context ID for the Description within the Help file.
• CancelDisplay is a required Boolean argument that controls whether VBA displays the error message in a message box.

DROPBUTTONCLICK EVENT

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 when the ComboBox has the focus (is selected). DropButtonClick also fires when the user presses 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.

SPINDOWN AND SPINUP EVENTS

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 as follows:

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.