Quick Jump
Introduction | Example | Tutorial | Applications | Comments

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

Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

Tutorial - VBA msoFileDialogFolderPicker

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

VBA msoFileDialogFolderPicker FileDialog
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
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
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
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.

Comments

If you haven’t already done so, please subscribe to my email list. What are you waiting for? You’ll love the great VBA content I send your way!

Now it’s time to have a discussion in the comments section.
Share this article on social media and follow me on Google+ and Twitter for even more great VBA content!


Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.