Displaying Office-Standard Dialog Boxes
Word and Excel let you display, alter, and retrieve data from any of their built-in dialog boxes. (There is no comparable capability in Outlook, PowerPoint, FrontPage, or Access.) These techniques can come in handy if you just want to tweak a built-in dialog box and you don't want to go through the considerable hassle of trying to completely re-create the dialog box.
Using Built-In Excel Dialog Boxes with VBA
Say you want to write a VBA/Excel macro that displays the Open dialog box, but instead of the default list of various Excel file types, you only want to look for Web pages (those with an .htm extension). Here's how:
Start Excel and press Alt+F11 to bring up the VBA Editor.
Navigate to a convenient module (possibly in the Personal Macro Workbook, Personal.xls) and double-click its entry.
To create a new macro, choose Insert, Procedure, type OpenHTM, and press Enter. VBA provides the Sub/End Sub pair.
Type this one-line program:
Application.Dialogs(xlDialogOpen).Show "*.htm"
To run the macro, click the Run Sub/User Form button (or press F5). Excel's Open dialog box shows only *.htm files (see Figure 40.15).
Figure 40.15. By putting a wildcard pattern in the File Name box, Excel looks only for files with the indicated name.
Excel enables you to replace parameters in built-in dialog boxes, but only by using positional arguments: The first argument after the .Show method must be the filename, the second argument concerns updating links, and so on. To see a full list of all the positional parameters available in all Excel dialog boxes (200+ of them), search for the Help topic "Built-In Dialog Box Argument Lists."
Using Built-In Word Dialog Boxes with VBA
Word, on the other hand, doesn't suffer from Excel's archaic positional fixations. The equivalent program in Word looks like this:
Dialogs(wdDialogFileOpen).Name = "*.htm" Dialogs(wdDialogFileOpen).Show
The .Show method in both Word and Excel displays the dialog box and interacts with the user in precisely the same way as if the user had brought up the dialog box.
Word uses a second method, .Display, that enables the user to make a selection from a dialog box but doesn't actually complete the normal operation of the dialog box. For example, you can use the .Display method with the Open dialog box to retrieve a filename; when the user selects a file and clicks Open, your program can use the specified filename without opening the file itself.
With Dialogs(wdDialogFileOpen)
.Name = "*.htm"
.Display
MsgBox "You chose File " & .Name
End With
Note
The With/End With command pair saves you a lot of typing. When you add a parameter after the With command, VBA tacks that parameter onto the beginning of every statement inside the With/End With block, if it begins with a dot. Thus, in the example here, VBA interprets .Name just as if you had entered Dialogs(wd.DialogFileOpen).Name.
These tricks all apply to standard, built-in Office dialog boxes—the dialog boxes you use every day, from Open to Save As to Find.
You need to open an entirely different bag of tricks to build and manipulate your own dialog boxes, working from scratch. That's the subject of the next section.