[Excel VBA] How to Display a Folder Selection Dialog (2 Methods)

When saving files created by macros or processing multiple files in a specific folder, you often want the user to select the target folder themselves.

VBA provides two main ways to display a “Folder Selection” dialog box. This article explains the features and usage of each method.

Method 1: Using Application.FileDialog (Recommended)

The Application.FileDialog object is a standard feature built into Excel. It allows you to display a modern folder selection dialog. The code is simple and easy to understand, making this the most recommended method today.

Completed VBA Code

Sub SelectFolder_WithFileDialog()

    ' Declare variables
    Dim folderPath As String
    
    ' Create FileDialog object in "Folder Picker" mode
    With Application.FileDialog(msoFileDialogFolderPicker)
        
        ' Set the title of the dialog
        .Title = "Please select a folder"
        
        ' Display the dialog and check if "OK" was pressed
        ' If .Show returns -1, it means OK was pressed
        If .Show = -1 Then
            ' Get the path of the selected folder
            folderPath = .SelectedItems(1)
            MsgBox "The following folder was selected:" & vbCrLf & folderPath
        Else
            ' If "Cancel" was pressed
            MsgBox "Folder selection was canceled."
        End If
        
    End With

End Sub

Explanation of Key Points

• Application.FileDialog(msoFileDialogFolderPicker): Creates a FileDialog object. Specifying msoFileDialogFolderPicker as the argument sets it to “Folder Selection” mode.
• .Show = -1: The .Show method displays the dialog. It returns -1 if the user clicks “OK” and 0 if they click “Cancel”, so you can branch the logic using an If statement.
• .SelectedItems(1): The full path of the folder selected by the user is stored in this property.

Method 2: Using Shell.Application (Legacy Method)

This is a method that has been used for a long time by calling Windows Shell functions. It displays a slightly older style of dialog box.

Completed VBA Code

Sub SelectFolder_WithShell()

    ' Declare variables
    Dim shellApp As Object
    Dim selectedFolder As Object
    Dim folderPath As String
    
    ' Create Shell.Application object
    Set shellApp = CreateObject("Shell.Application")
    
    ' Display folder selection dialog
    ' Arguments: (Window Handle, Title, Options)
    Set selectedFolder = shellApp.BrowseForFolder(0, "Please select a folder", 0)
    
    ' Check if a folder was selected
    If Not selectedFolder Is Nothing Then
        ' Get the path of the selected folder
        folderPath = selectedFolder.Self.Path
        MsgBox "The following folder was selected:" & vbCrLf & folderPath
    Else
        ' If "Cancel" was pressed
        MsgBox "Folder selection was canceled."
    End If
    
    ' Release objects
    Set selectedFolder = Nothing
    Set shellApp = Nothing

End Sub

Explanation of Key Points

• CreateObject("Shell.Application"): Creates an object to manipulate Windows Shell functions.
• .BrowseForFolder(...): The method used to display the folder selection dialog.
• If Not selectedFolder Is Nothing Then: If the user presses the “Cancel” button, the return value becomes an empty object (Nothing). This is used to check the result.

Summary

In conclusion, if you are writing new code, using the simple and highly functional Application.FileDialog method is best.

Allowing users to select folders greatly improves the flexibility and convenience of your macros. Please master this technique to create more user-friendly VBA tools.