SaveAs Result?

[Shawn Shuler]

I'm having trouble with adding a feature to prompt the user to save the file
under a different name. I need it to force the user to save it. The
trouble is that if the user chooses a file name that exists he/she is asked
to overwrite the file or not. If I choose 'No' or 'Cancel' it creates an
error. I can trap the error and try to resume but all this does is loop
through the overwrite prompt, effectively forcing the user to overwrite the
file.

Is there a method to determine and handle the output from the overwrite
dialog, make choices based on which button was pressed?

 

[Tom Ogilvy]

Use GetSaveAsFilename to put up your own prompt for the filename. Then you
can also manage overwriting and the user's response.

 

[Shawn Shuler]

I am doing that but it still uses the standard Save As dialog box. If I
were to change the filename in there to one that exists I end up in the same
place with the same problem. Is there no way to determine which button (Ok,
No, Cancel) is pressed and then handle each possible choice?

 

[Tom Ogilvy]

GetSaveAsFileName may show the saveas dialog, but it doesn't attempt to save
the file. You do that with your code. If you are doing that, then you
should be able to add code to deal with an existing file name. If excel is
putting up the dialog, then there is no way to determine what the user has
answered. But as I said, you can write your code so Excel doesn't put up
the dialog.

 

[Shawn Shuler]

Hmmmm, I see what you are saying. I'll have to look again (have to actually
work for money right now) when I get home. I may just be handling things in
a bad sequence (determining WHERE to save the file too late in the process).
I'll take a fresh look at it this afternoon. I appreciate the help.