Folder Picker Excel VBA & paste Path to Cell

Question

I am having difficulty figuring out how to put the Folder Path in Cell C49. I'd like to have the Path there for the User to understand where they are searching and if they have to change said Path.

I got this VBA code from, http://learnexcelmacro.com/wp/2016/12/how-to-open-file-explorer-in-vba/

Private Sub cmd_button_BROWSEforFolder_Click()

    On Error GoTo err
    Dim fileExplorer As FileDialog
    Set fileExplorer = Application.FileDialog(msoFileDialogFolderPicker)

    'To allow or disable to multi select
    fileExplorer.AllowMultiSelect = False

    With fileExplorer
        If .Show = -1 Then 'Any folder is selected
            [folderPath] = .SelectedItems.Item(1)
            ThisWorkbook.Sheets("Home").Range("C49") = .SelectedItems.Item(1)
        Else ' else dialog is cancelled
            MsgBox "You have cancelled the dialogue"
            [folderPath] = "" ' when cancelled set blank as file path.
        End If
        End With
err:
    Exit Sub

End Sub

I've tried rearranging the location of,

ThisWorkbook.Sheets("Home").Range("C49") = .SelectedItems.Item(1)

and tried changing

.SelectedItems.Item(1)

to, [folderPath] with no prevail.

what am I missing? all I need is the path to be displayed above the txtbox and if it needs to be changed then the User used the button to redirect the search. (this button will not initiate the search Macro)

https://stackoverflow.com/questions/16917122/defaulting-a-folder-for-filedialog-in-vba — Marcucciboy2, Nov 01 '18 at 17:16
@Marcucciboy2 thanks for responding, but I'm not sure what I'm suppose to use from the link you commented. I'm thinking i have to turn the Selected Folder into a String Dim'd object then link .range ("C49 ") = Selected Folder — Kenny, Nov 01 '18 at 18:02
Yeah, my suggestion is more of a replacement for your code above instead of an example for fixing the [folderpath] — Marcucciboy2, Nov 01 '18 at 18:07

score 4 · Accepted Answer · answered Nov 01 '18 at 18:25

Private Sub cmd_button_BROWSEforFolder_Click()

    On Error GoTo err
    Dim fileExplorer As FileDialog
    Set fileExplorer = Application.FileDialog(msoFileDialogFolderPicker)
    Dim folderPath As String

    'To allow or disable to multi select
    fileExplorer.AllowMultiSelect = False

    With fileExplorer
        If .Show = -1 Then 'Any folder is selected
            folderPath = .SelectedItems.Item(1)

        Else ' else dialog is cancelled
            MsgBox "You have cancelled the dialogue"
            folderPath = "NONE" ' when cancelled set blank as file path.
        End If
    End With
err:

ThisWorkbook.Sheets("Home").Range("C49") = folderPath

End Sub

you forgot to add `Exit Sub` before `err:` . Thank you for the code) — Mik, Dec 19 '22 at 10:16

Folder Picker Excel VBA & paste Path to Cell

1 Answers1

Linked