Get File Path (ends with folder)

Question

I know how to let the user click on a button to navigate to a specific file to open.

Code:

Private Sub CommandButton2_Click()
    Dim vaFiles As Variant

    vaFiles = Application.GetOpenFilename()

    ActiveSheet.Range("B9") = vaFiles
End Sub

I want a second button that will let the user navigate to a folder to save the .pdf file that my program creates.

The problem: The GetOpenFilename requires the user to click on a file. If there's no file in the folder then there's nothing the user can do.

Answer 1

Use the Application.FileDialog object

Sub SelectFolder()
    Dim diaFolder As FileDialog
    Dim selected As Boolean

    ' Open the file dialog
    Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
    diaFolder.AllowMultiSelect = False
    selected = diaFolder.Show

    If selected Then
        MsgBox diaFolder.SelectedItems(1)
    End If

    Set diaFolder = Nothing
End Sub

Answer 2

In the VBA Editor's Tools menu, click References... scroll down to "Microsoft Shell Controls And Automation" and choose it.

Sub FolderSelection()
    Dim MyPath As String
    MyPath = SelectFolder("Select Folder", "")
    If Len(MyPath) Then
        MsgBox MyPath
    Else
        MsgBox "Cancel was pressed"
    End If
End Sub

'Both arguements are optional. The first is the dialog caption and
'the second is is to specify the top-most visible folder in the
'hierarchy. The default is "My Computer."

Function SelectFolder(Optional Title As String, Optional TopFolder _
                         As String) As String
    Dim objShell As New Shell32.Shell
    Dim objFolder As Shell32.Folder

'If you use 16384 instead of 1 on the next line,
'files are also displayed
    Set objFolder = objShell.BrowseForFolder _
                            (0, Title, 1, TopFolder)
    If Not objFolder Is Nothing Then
        SelectFolder = objFolder.Items.Item.Path
    End If
End Function

Source Link.