Displaying Excels Builtin Dialog Boxes

You can write VBA code that performs the equivalent of selecting an Excel menu command and making choices in the resulting dialog box — although Excel doesn't actually display the dialog box.

For example, the following statement has the same effect as choosing the HomeOEditingOFind & SelectOGo To command, specifying a range named InputRange, and clicking OK:

Application.Goto Reference:="InputRange"

When you execute this statement, the Go To dialog box does not appear. This is almost always what you want to happen; you don't want dialog boxes flashing across the screen while your macro executes.

In some cases, however, you may want your code to simply display one of Excel's many built-in dialog boxes and let the user make the choices in the dialog box. You can do this by using the Application object's Dialogs property. Here's an example:

Result = Application.Dialogs(xlDialogFormulaGoto).Show

When executed, this statement displays the Go To dialog box, as shown in Figure 15-8. The user can specify a named range or enter a cell address. This dialog box works exactly as it does when you choose HomeOEditingOFind & SelectOGo To or press F5.

Figure 15-8:

The Go To dialog box, displayed by using VBA code.

Figure 15-8:

The Go To dialog box, displayed by using VBA code.

Special,

You may think that the value assigned to the Result variable is the range that the user selects in the Go To dialog box. Actually, the value assigned to Result a Boolean value: True if the user clicks OK, and False if the user clicks Cancel or presses Escape.

The preceding example uses the predefined constant xlDialogFormulaGoto. This constant determines which dialog box Excel displays. To display Excel's Open dialog box, use this statement:

Application.Dialogs(xlDialogOpen).Show

You can get a list of available dialog box constants by using the Object Browser. Follow these steps:

The Object Browser appears.

2. In the Project/Library drop-down list (the one at the upper-left corner of the Object Browser), select Excel.

3. In the Search Text drop-down list (just below the Project/Library dropdown list) type xlDialog.

4. Click the Search button (the button with the binoculars).

Figure 15-9 shows the Object Browser displaying a list of the dialog box constants.

Figure 15-9:

Use the Object Browser to get a list of the dialog box constants.

Figure 15-9 shows the Object Browser displaying a list of the dialog box constants.

Unfortunately, these dialog box constants are not documented in the Help system. Therefore, you may need to use a bit of trial and error to figure out which is appropriate for your needs. You'll find that some Excel dialog boxes don't have a corresponding dialog box constant. Even worse, some of the dialog box constants just don't work.

If you try to display a built-in dialog box in an incorrect context, Excel displays an error message. For example, one of the dialog box constants is xlDialogAlignment. This dialog box sets text alignment in a cell. If you try to display this dialog box when something other than a range is selected, Excel displays an error message because that dialog box is appropriate only for worksheet cells.

0 0

Post a comment