With Application.FileDialog(msoFileDialogFolderPicker)
.Show

MsgBox .SelectedItems(1)

End With

Look up FileDialog in the VBA help


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ANDYGM" <[email protected]> wrote in
message news:[email protected]...
>
> I have a macro which saves the spreadsheet to a new directory (currently
> the directory is specified within the macro). That's OK for me but I
> want to distribute the spreadsheet to a wider audience and to make it
> easy for them I'd like to include a piece of code which prompts them
> for the directory to save to, just the same as when you do a 'Save As'
> from the spreadsheet.
>
> As some of the directory trees are quite complex, I can't ask them to
> type the directory location.
>
> Any ideas?
>
>
> --
> ANDYGM
> ------------------------------------------------------------------------
> ANDYGM's Profile:
http://www.excelforum.com/member.php...fo&userid=3452
> View this thread: http://www.excelforum.com/showthread...hreadid=531463
>