Modal Forms

The Show() method of the UserForm object takes an optional Boolean parameter that specifies whether or not the form is modal. The default value of the modal parameter is true, which creates a modal form. A modal form is one that must be addressed by the user, and subsequently closed (by the user or the program) before any other part of the Excel application can be accessed. If the form is modeless, then the user may select between any open windows in the Excel application.

Modeless forms are only supported ¡n MS Office 2000, and later versions. Trying TC^CV to create a modeless form in an earlier version of MS Office will generate a run-^-"XjJ time error.

Use the VBA-defined constants vbModal and vbModeless with the Show() method to show modal and modeless forms, respectively.

A modal form is safest, unless user interaction with the Excel application is required while the form is displayed. The form can be displayed via the Show() method from anywhere in a VBA program; however, be aware that program execution may proceed differently depending on where in a procedure the form is shown and whether the form is modal. For example, the two procedures below will yield different results.

In the first example, the Show() method is called for a UserForm object in order to display a modeless form. Next, a MsgBox() function displays some text. In this example, code execution proceeds through the entire procedure—first displaying the form, then the message box—so both dialogs are displayed to the user at the same time.

Private Sub MyProcedure()

frmMyUserForm.Show vbModeless

MsgBox("The message box is displayed immediately after the UserForm") End Sub

In the second example, the form is displayed modally, enabling code execution within the procedure to pause while the form is displayed. After the user closes the form, program execution proceeds to the next line of code; thus, when using a modal form, program behavior is identical to the MsgBox() and InputBox() functions.

Private Sub MyProcedure()

frmMyUserForm.Show vbModal

MsgBox("The message box is displayed after the UserForm is closed.") End Sub

To determine which version of Excel is running on a user's computer, test the Version property of the Application object. The Version property returns a read-only string containing a number that represents the version of Excel currently running on your computer (for example, 11.0 for Excel 2003).

Now that you know how to display a form in a program, it's time to look at a few specific ActiveX controls that are used with forms and see how they interact with the Excel application.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook

Post a comment