Programming Excel with VBA and .NET

Display Dialogs

The three different sorts of dialog boxes in Excel are built-in dialogs that perform actions, built-in dialogs that return information, and custom dialogs you build from Visual Basic forms. The Application object gives you several ways to display the first two types:

• Use the FindFile method to let the user select a file to open in Excel.

• Use the Dialogs collection to display Excel’s other built-in dialog boxes to perform those specific actions.

• Use FileDialog method to get file and folder names from the user.

• Use the InputBox method to get ranges or formulas.

For example, the following code displays Excel’s built-in Open dialog box and then opens the file selected by the user:

Sub OpenFile1(  )
    On Error Resume Next
    Application.FindFile
    If Err Then Debug.Print "User cancelled import."
End Sub

You can do the same thing using the Dialogs collection:

Sub OpenFile2(  )
    On Error Resume Next
    Application.Dialogs(XlBuiltInDialog.xlDialogOpen).Show
    If Err Then Debug.Print "User cancelled import."
End Sub

Both of the preceding samples display the Open dialog box and open the file in Excel. You have to include error-handling statements in case the user chooses a non-Excel file then cancels importing the file—otherwise that action halts your code with an application error.

The Dialogs collection can display any of the Excel dialog boxes. See Appendix A for a list of those dialogs—about 250 of them! Displaying a dialog that way is just like displaying it through the user interface: Excel ...