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?

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

--
Regards,
Tom Ogilvy

"Shawn Shuler" wrote in message
...
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?

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?

"Shawn Shuler" wrote in message
...
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?

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.

--
Regards,
Tom Ogilvy


"Shawn Shuler" wrote in message
...
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?

"Shawn Shuler" wrote in message
...
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?

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.

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Shawn Shuler" wrote in message
...
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?

"Shawn Shuler" wrote in message
...
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?