Displaying Built-in Dialog Boxes with Excel VBA

VBA allows you to programmatically display Excel’s built-in dialog boxes, in addition to user-defined dialog boxes. All built-in Excel dialog boxes form the Dialogs collection, where the parameter specifies which dialog box to activate.

Displaying a built-in dialog box on the screen is done using the Show method.

For example, the procedure activates the Open dialog box when a button is clicked.

Displaying the Open dialog box

Private Sub CommandButton1_Click()
    Application.Dialogs(xlDialogOpen).Show
End Sub

Table. Values of Dialogs collection parameter

Within the Show method, you can specify parameter values that prefill fields in the dialog box.

For example, when displaying the Goal Seek dialog box, you can specify:

• target_cell (Set Cell field),
• target_value (To Value field),
• variable_cell (By Changing Cell field).

This results in the dialog box appearing with its input fields already filled.

Displaying Goal Seek with predefined values

Private Sub DoGoalSeek()
    Dim fl As Boolean
    fl = Application.Dialogs(xlDialogGoalSeek).Show(Range("A1"), 0, Range("A2"))
    If fl Then
        MsgBox "Solution found"
    Else
        MsgBox "Solution not found"
    End If
End Sub

Open Dialog and the GetOpenFilename Method
There is another way to display the Open dialog box—using the GetOpenFilename method of the Application object.

This method displays the Open dialog box, but it does not open the selected file. Instead, it simply returns the name of the selected file, or the value False if no file was selected.

To open the selected file, you must additionally use the Open method of the Workbooks collection.

Opening a file

Sub OpenDoc()
    Dim FName As Variant
    FName = Application.GetOpenFilename( _
        "Microsoft Excel Workbook (*.xlsx), *.xlx")
    If FName <> False Then
        Workbooks.Open FName
    Else
        MsgBox "No file selected"
    End If
End Sub

The appearance of the dialog box can be controlled by setting parameters of the GetOpenFilename method.

Syntax:

GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect)

• FileFilter — Optional. A string specifying the filter for displayed files. If omitted, defaults to « All files (*.*), *.* ».
  Example: « Microsoft Excel Workbook (*.xlsx), *.xlsx, Bitmap Files (*.bmp), *.bmp ».
• FilterIndex — Optional. Sets which filter index is used by default.
• Title — Optional. Sets the title of the dialog box.
• ButtonText — Used only in Mac OS X.
• MultiSelect — Optional. Boolean. If True, allows selection of multiple files. In that case, the method returns an array of file names instead of a single string.

Since GetOpenFilename only returns file names (without acting on them), the dialog box can also be used for purposes other than opening files—for example, deleting selected files.

Deleting files

Sub DeleteFile()
    Dim FName As Variant
    FName = Application.GetOpenFilename( _
        FileFilter:="Microsoft Excel Workbook (*.xls), *.xls", _
        MultiSelect:=True, Title:="Delete File")
    If Not IsArray(FName) Then
        MsgBox "No file selected"
        Exit Sub
    End If
    Dim i As Integer
    For i = LBound(FName) To UBound(FName)
        Kill FName(i)
    Next
End Sub

A Simple Graphic File Browser
The GetOpenFilename method returns the name of any type of file, including bitmap files.
At the same time, an Image control can display any bitmap file.

Together, these two facts make it possible to create a simple tool for browsing bitmap images.

So, create a form with a button and an Image control, and in the form module type the necessary code.

The project is ready.

Saving a Document and the GetSaveAsFilename Method
The GetSaveAsFilename method of the Application object displays the Save As dialog box.

Like the GetOpenFilename method, this method does not itself save the file—it only returns the name of the file chosen in the dialog box.
To actually save the file, you must extend the code with a statement that uses either the SaveAs or Save method of the Workbook object.

Syntax

GetSaveAsFilename(InitialFilename, FileFilter, FilterIndex, Title, ButtonText)

• InitialFilename — Optional. Any valid file name that will appear in the File name field. (By default, Excel suggests the name Book.)
• FileFilter — Optional. Sets the filter for the displayed files. If you want a file extension to be automatically added to the file name, this parameter must be used.
• FilterIndex — Optional. Indicates which filter from the File types list will be used by default.
• Title — Optional. Sets the dialog box title.
• ButtonText — Used only in Mac OS X.

Example
The following code can be used when saving a workbook, where by default the suggested file name is Report.

Saving a file

Sub SaveDoc()
    Dim FName As Variant
    FName = Application.GetSaveAsFilename(InitialFilename:="Report", _
        FileFilter:="Microsoft Excel Workbook (*.xlsx), *.xls")
    If FName <> False Then Application.ThisWorkbook.SaveAs FName
End Sub