VBA to prompt user to select location to save file

[Bevg]

Hi,

I'm trying to figure out how to have VBA check the path before it saves and it does not match, then a pop up box opens and the user can select the drive and folder where they want the file saved.

Right now I have the location hard coded with the path to my C drive. Now several people need to use this VBA code and I don't want them to go into the VBA to change the path.

Any help would be greatly appreciated.

Here is my code

Please Login or Register  to view this content.

[jmac1947]

Good morning bevg,

It is always a good idea to post a sample workbook with your query, it makes it considerably easier for us to actually test any proposed solution before we respond.

That having been said you could use something like as a simple first step:

FPath = InputBox("Please enter full path name to save file", "Input required path name")

Of course, if you are expecting others to use the spreadsheet then an element of checking would be needed to ensure path exists etc.

[Bevg]

Hi,
thank you for the suggestion, 6StringJazzer said that I needed to wrap my code in [CODE][\CODE] and therefore it can be copied. So I'm getting conflicting direction.

Anyways, I was looking at the ENVIRON(Username or Userprofile) it seems that this will not need a check statement and will automatically select the individuals desktop. Have you ever been successful with this syntax?

If so how did you use it

Thanks,

Bev

[6StringJazzer]

In Windows, the path to the desktop is:

Environ("USERPROFILE") & "\Desktop"

You can use this in your code above to assign to FPath.