VBA Folder Picker: msoFileDialogFolderPicker

The functions below let the user pick a folder in the local file system and then return the path of the selected folder as a string, or return an empty string if the user cancels.

Basic version

VBA
Function FolderPicker() As String
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then FolderPicker = .SelectedItems(1)
    End With
    
End Function

Usage example

VBA
Sub TestFolderPicker()
    Dim folderPath As String
    folderPath = FolderPicker()
    Debug.Print "Folder path: " & folderPath
End Sub

Advanced version

VBA
Function FolderPickerAdvanced(Optional strTitle As String, Optional strDefaultFolderPath As String) As String
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        If strTitle <> vbNullString Then .Title = strTitle
        If strDefaultFolderPath <> vbNullString Then .InitialFileName = strDefaultFolderPath
        If .Show = -1 Then FolderPicker = .SelectedItems(1)
    End With
    
End Function

Parameters

Usage example

VBA
Sub TestFolderPickerAdvanced()
    Dim folderPath As String
    folderPath = FolderPickerAdvanced("Pick a folder", "C:\test\folder1\")
    Debug.Print "Folder path: " & folderPath
End Sub