The Show Method

The Show method displays a dialog box. This provides a convenient way to "lead" the user to a built-in dialog box. Unfortunately, we cannot access the values that the user enters into that dialog. Until the dialog is dismissed by the user and the actions specified in the dialog are completed, we have no control over the chain of events. (In Word 97, for instance, we can use built-in dialog boxes to get values from the user, without letting Word act automatically on those values.)

To illustrate, the code:

Application.Dialogs(xlDialogOpen).Show displays the Open dialog box in Figure 13-1. The Show method returns True if the user clicks the OK button and False if the user clicks the Cancel button.

When the dialog box is dismissed by the user using the OK button, any appropriate actions indicated by the fields in the dialog box are carried out. In the case of the Open dialog, this means, of course, that the file selected by the user is actually opened in Excel. However, no actions are taken if the user dismisses the dialog box using the Cancel button.

The Show method has syntax:

DialogObject.Show(arg1, arg2, ..., arg30) where the arguments are used to set some dialog options.

In particular, it is possible to set some of the values on a built-in Excel dialog box using arguments to the Show method. These arguments are listed in the Excel VBA Help file under "Built-In Dialog Box Argument Lists." For instance, the xlDialogOpen dialog box has the following arguments:

file text update links read_only format prot pwd write res pwd ignore rorec file origin custom delimit add logical editable file access notify logical converter

Hence, the code:

Application.Dialogs(xlDialogOpen).Show "*.*", False, True displays the Open dialog, sets the "Files of type" drop-down box to All Files "*.*" so that the dialog will display the names of all files, sets update_links to False (so that Excel links are not automatically updated) and read_only to True (thus any file that is opened will be readonly).

Unfortunately, Microsoft does not seem to have documented the meaning of the various arguments. Also, the arguments are not named arguments, so we must include space for all arguments that precede the arguments that we want to set. Thus, a trial-and-error approach seems to be the only solution if you must set some dialog options. (Have fun.)

0 0

Post a comment