VBA Select Folder with msoFileDialogFolderPicker

Introduction - VBA msoFileDialogFolderPicker

Use msoFileDialogFolderPicker to browse for and select a folder with VBA. The msoFileDialogFolderPicker is part of the Application.FileDialog family in Excel. The msoFileDialogFolderPicker is just one of many Application.FileDialog MsoFileDialogType boxes.

Note, the Application.FileDialog property works with most Microsoft Office products after 2010, but it won’t work with all Microsoft products. For example, trying to display a FileDialog window in Outlook will generate run-time error 438. To get the Folder Picker to work in Outlook, you must make reference to an instance of Excel or Word from within your Outlook VBA.

Example - VBA msoFileDialogFolderPicker

Sub SelectFolder()
Dim sFolder As String
    ' Open the select folder prompt
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then ' if OK is pressed
            sFolder = .SelectedItems(1)
        End If
    End With
    
    If sFolder <> "" Then ' if a file was chosen
        ' *********************
        ' put your code in here
        ' *********************
    End If
End Sub

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

Tutorial - VBA msoFileDialogFolderPicker

When you execute the macro above, you’ll get a folder picker dialog box, like this:

VBA msoFileDialogFolderPicker Prompt

The .Show command displays the folder picker dialog box and when the dialog box closes, .Show can have 2 possible values: a -1 or a 0.

• -1 - A folder is selected and the OK button is pressed
• 0 - The Cancel button or the X is clicked.

If a folder is selected and OK is clicked, the full folder path is stored in the variable sFolder. If cancel is clicked, the variable sFolder will be blank.

Customizing the Folder Picker

As you might have imagined, there are a few ways you can customize how your folder picker will be presented. All customizations must be done BEFORE you issue the .Show command, since .Show is what calls and renders the dialog box.

I’m going to show you how to customize your folder select prompt by changing the following properties:

• .Title
• .ButtonName
• .InitialFileName

Once I’m done showing you, you’ll be able to combine these properties into one awesome prompt!

Now is a good time to tell you the msoFileDialogFolderPicker will not allow you to select multiple folders, even if you set the .AllowMultiSelect property to True. You also can’t apply filters like you can with other MsoFileDialogType boxes. That makes sense, because there’s nothing to filter!

Changing the Title

You can change the title appearing in the upper left of your dialog box from the default “Browse” to whatever text you want by modifying the .Title property, like this:

Sub SelectFolder2()
Dim sFolder As String
    ' Open the select folder prompt
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a Folder"
        If .Show = -1 Then ' if OK is pressed
            sFolder = .SelectedItems(1)
        End If
    End With
    
    If sFolder <> "" Then ' if a file was chosen
        ' *********************
        ' put your code in here
        ' *********************
    End If
End Sub

VBA Folder Picker Custom Title

You have to change the .Title property BEFORE you issue the .Show command. Otherwise, the default text will display.

Changing the OK Button Text

In addition to changing the title of your msoFileDialogFolderPicker dialog prompt, you can also change the text on your “OK” button by editing the .ButtonName property.

Sub SelectFolder3()
Dim sFolder As String
    ' Open the select folder prompt
    With Application.FileDialog(msoFileDialogFolderPicker)
        .ButtonName = "Select"
        If .Show = -1 Then ' if OK is pressed
            sFolder = .SelectedItems(1)
        End If
    End With
    
    If sFolder <> "" Then ' if a file was chosen
        ' *********************
        ' put your code in here
        ' *********************
    End If
End Sub

VBA Folder Picker OK Button Text

Just like with the title card, you have to change the .ButtonName property BEFORE you issue the .Show command.

Setting the Default Folder

The .InitialFileName property is used to set where you want the file picker to go by default. By setting it to a valid directory path, the Browse for a Folder dialog box will initialize in that location.

Sub SelectFolder4()
Dim sFolder As String
    ' Open the select folder prompt
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "C:\Windows\"
        If .Show = -1 Then ' if OK is pressed
            sFolder = .SelectedItems(1)
        End If
    End With
    
    If sFolder <> "" Then ' if a file was chosen
        ' *********************
        ' put your code in here
        ' *********************
    End If
End Sub

VBA Folder Picker Default Folder

Make sure you pass .InitialFileName a valid folder string.

Application Ideas - VBA msoFileDialogFolderPicker

Displaying a Folder Selection window is really useful when you want to export worksheets as PDFs to certain folder, but don’t limit yourself to just using this in Excel! I use a modified version in Outlook to archive my messages to a folder of my choosing and to create error log files in that directory.

In this tutorial, we showed you how to use the msoFileDialogFolderPicker to allow the end-user to interactively choose folders. This is very helpful, but if you want to become a true file I/O expert, you should look at our comprehensive VBA File I/O Cheat Sheet filled with 50+ tips for working with files and over 30 file input/output macro examples.

If you haven’t already done so, please subscribe to my free wellsrPRO VBA Training Program using the form below. What are you waiting for? You’ll love the great VBA content I send your way!