Displaying Built-in Microsoft Excel Dialog Boxes

In addition to the message box and the input box, Microsoft Excel has approximately 200 built-in dialog boxes. Each dialog box allows the user to perform actions that change some feature of Microsoft Excel. For example, the built-in File Open dialog box allows the user to open a file, and the Clear dialog box allows the user to clear a range of cells.

The Dialogs method returns a built-in dialog box. This method takes as an argument a built-in constant that begins with "xlDialog" and that corresponds to a dialog box name. For example, the constant for the Find File dialog box is xlDialogFindFile.

The Show method displays the dialog box. You cannot replace the dialog box functionality (write your own file open code, for example) or trap the events that occur in the dialog box. The dialog box functions normally, and Microsoft Excel performs any actions the user requests.

The following example displays the built-in File Open dialog box, with the default folder set to Xlfiles.

Application.Dialogs(xlDialogOpen).Show("C:\XLFILES")

The Show method doesn't return to the calling procedure until the user has dismissed or canceled the dialog box and Microsoft Excel has completed any requested actions. The return value is True if the user clicked OK or pressed ENTER to dismiss the dialog box; the return value is False if the user clicked Cancel or pressed ESC to cancel the dialog box. The following example runs the ProcessWorkbook procedure if the user clicks a workbook in the File Open dialog box.

Sub NewWorkbook()
    If Application.Dialogs(xlDialogOpen).Show("c:\") And _
        ActiveWindow.Type = xlWorkbook Then ProcessWorkbook
End Sub

The Show method may fail if you try to show a dialog box in an incorrect context. For example, the method fails if you attempt to display the Format Data Labels dialog box (using the Visual Basic expression Application.Dialogs(xlDialogDataLabel).Show) when the active sheet isn't a chart.

You cannot copy a built-in dialog box, but you can create a custom dialog box that looks just like the built-in dialog box and write a series of procedures that duplicates the features of the built-in dialog box. You can then add and delete controls and change their behavior. For more information about creating and using custom dialog boxes, see "Using Custom Dialog Boxes" later in this chapter.

Note

Visual Basic does provide some methods that allow you to use duplicates of built-in dialog boxes. For example, the GetOpenFileName method displays the File Open dialog box and returns the filename the user selects. The GetSaveAsFileName method displays the File Save As dialog box.