The following function will do what you want, but I'm not sure which version
of XL you need to get the "FolderPicker". Copy the following "Sub" and
"Function" into a standard module - then run the "Sub":

Sub test()

Dim sFolder As String
sFolder = FolderSelectPrompt
MsgBox sFolder
End Sub

Public Function FolderSelectPrompt(Optional argPath As String) As String
'CALL CAUSES FOLDER SELECTION DIALOG BOX TO APPEAR;
'FUNCTION RETURNS THE FOLDER SELECTED BY THE USER;
'Create a FileDialog object as a File Picker dialog box
Dim dlgFolder As FileDialog
Set dlgFolder = Application.FileDialog(msoFileDialogFolderPicker)
With dlgFolder
.AllowMultiSelect = False
.ButtonName = "RUN!"
.InitialView = msoFileDialogViewDetails
.InitialFileName = argPath
.Title = "RUN PROGRAM!"
'.Show
End With
If dlgFolder.Show = -1 Then
FolderSelectPrompt = dlgFolder.SelectedItems(1) & "\"
Else
End
End If
End Function

HTH

"Nigel" wrote:

Hi,

can a macro be written to change a path set a macro to another location of
your choice? I.E.

my current macro saves to "s:\Invoicing\Sales 05 06" but as we have remote
users, this path changes because they need these files to save locally too. i
was wondering if i included a button, they could start the command, open a
dialog box and select their new location to save. This would then be the set
location on the macro until it is changed. Is this possible?

Thanks,

Nigel