It’s a common occurrence to need to prompt the caller of a VBA subprocedure at runtime for a directory location for any number of reasons. It is far more efficent to present the caller of the subprocedure with a graphical interface as opposed to a text entry dialouge, since entering absolute file paths directly is clumsy and error prone.
The following example opens a Windows Explorer window, and the caller will
then select a directory location. Once selected, the subprocedure will write
the selected directory path to cell
A1 of the
'=================================================== ' Present caller with directory selection dialouge | '=================================================== Sub SelectDirectory() Dim sht As Worksheet: Set sht = ThisWorkbook.Worksheets("Input") Dim selectedLocation As Range: Set selectedLocation = sht.Range("A1") ' Clear cell `A1` => selectedLocation.ClearContents With Application.FileDialog(msoFileDialogFolderPicker) .AllowMultiSelect = False .Show If .SelectedItems.Count > 0 Then selectedLocation.Value = CStr(.SelectedItems(1)) End If End With End Sub