Error Handling - No or Cancel throws up Error

Hi

On saving a workbook to a folder the expected dialogue box "A file by this name already exist. Do you want to overwrite it?" comes up. The answer is usually "Yes" and everything moves ahead sweetly. However, if the answer is "No" or you click "Cancel" it throws up an error.

I've tried to use an error handling code I found on the Internet and have fiddled with it, but can't get it to work for me.

The relevant bit of my code with the error handler in Brown is as follows:

Please Login or Register  to view this content.

I appreciate that this is the subject of many queries and I've read many of them; attempted to put an error handler in my code but without success and now need some help please.

Many thanks in advance

Frankie

Replace or add as required and try it.

Please Login or Register  to view this content.

Hi Jolivanes

Sorry, It's taken a bit of time to get back on this. The Code works well thanks, but it leaves the workbook that has been produced open. I did a small modification and added wb.Close SaveChanges:=False after the "I had enough" message but that message came up when I actually clicked "OK" on the original "A file already exists……" message.

How would I get the Code to close the produced workbook after the error message box has been closed please?

Show us your whole code please.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Nearly there! This works OK, but takes away the option to overwrite the existing sheet as it may well be that the sheet being produced has had some changes to it that need to be save. The original issue is that if a file of the same name exists, Excel (?) puts up a Yes - No - Cancel choice for saving the sheet. If you click "Yes" everything is good and the sheet in the folder is overwritten. However, if you press "No" or "Cancel" it threw up an error (1004 Method 'Save As' of object '_Workbook' failed). I'm looking to be able to either click "Yes" and save / overwrite the sheet, or click on "No" or "Cancel" close the sheet produced without saving and end the Code.

Thanks for the continued responses. Very much appreciated!

It looks like we're talking of different things.
The code from Post #6 should only save a sheet as a workbook.
I see now that you have the line

Please Login or Register  to view this content.

twice.
The code from Post #6 should replace below the 2nd time it appears in your supplied code from Post #5.

Is that not the problem you're talking about?

It'd bed time now so it'll be a while.

I added the code under the second "If eCell….." which is commented out (a result of previous changes!).

If there was a file of the same name already in the folder the original code at post 1 would bring up the "Yes" "No" "Cancel" dialogue box after the code lines


If I clicked "Yes" everything was fine and the sheet saved overwriting the current one. Clicking "No" or "Cancel" would cause the cause the code fall over (End / Debug message)
I fiddled around adding the code in brown (in post 1) but failed quite badly to achieve anything!

What we may aim at is getting the message box to say something along the lines of "The file:" & vbLf & fName & vbLf & "already exists. Do you want to overwrite it?" If we then had a "Yes" and a "No" button that saved / overwrote the file on "Yes" or does what the error handling code in post 6 does if you click on "No", we should be about right.

If you click "Yes" the next message box should then say "The file:" & vbLf & fName & vbLf & " has been saved". (It should say this when the sheet is saved even if there isn't file to overwrite too). Having a message that says "The file has not been saved" after clicking "No" or "Cancel" would be great too! (Some of the potential users are not very computer savvy so the more info I can give them the better!)

Sleep well. I should be doing the same in a couple of hours (just on Friday night rather than Thursday!) but making excel do magic things with this coding lark keeps me awake quite often!!

Frankie

After the code lines

Please Login or Register  to view this content.

Didn't want to risk having the post wiped again by editing it!!!

Xlsx.
xlsm files don't seem to work from shared folders which is where I have to save them

HA! (again!) I spent all morning working on my version only to find a second page to the thread and the response above (which I've added a few message boxes to show the file saved or not) and which works perfectly!!!

Thank you Bakerman2 and Jolivanes for your excellent responses and for getting me moving again!

Very grateful to you both.

Frankie