When your VBA applications are required to open a file you can use the Windows Common Dialog Control to utilize the standard Windows Open file dialog.

Add a Reference to the Control

1. In the VBA Editor select Tools > Additional Controls… from the top pull-down menu.
   • If this command is grayed out open a Form and turn on the Toolbox display (View > Toolbox) to enable
2. Scroll down the list of available controls, select the Microsoft Common Dialog Control, and click OK
   • This will add the Common Dialog control to the Toolbox.

Add the Control to a Form

3. Select the Common Dialog icon from the Toolbox
   
4. Draw a box on the form to add the control.
   • The control appears as a small icon on your form but will not display at runtime
     

Add code for the ShowOpen method

1. Add the code to use the File Open dialog

Private mFileName As String

Private Sub cmdOpenFile_Click()
    On Error GoTo NO_FILE

    With CommonDialog1
        'Raise an error if user clicks cancel button
        .CancelError = True

        'Initial directory contents to display in dialog
        .InitDir = Environ$("USERPROFILE") & "\My Documents"

        'Default file name
        .FileName = ""

        'File type filter for available file types
        .Filter = "Text Files (*.txt)|*.txt|All files (*.*)|*.*"

        'Flags control the behavior of the dialog box
        .Flags = FileOpenConstants.cdlOFNExplorer + FileOpenConstants.cdlOFNHideReadOnly

        'Display the File Open dialog
        .ShowOpen

        'Store the file name entered
        mFileName = .FileName
    End With

    Exit Sub

NO_FILE:
    'User clicked cancel so no file was selected
    mFileName = ""
End Sub

Test it out

Clicking on the Open button on our form will display the standard Windows Open dialog.

Other Methods

In addition to the ShowOpen method the Common Dialog control supports these methods for standard Windows dialogs:

• ShowColor
• ShowFont
• ShowHelp
• ShowPrinter
• ShowSave