Microsoft Office Forums - View Single Post - Error Handler If User Does Not Want to Overwrite File

BobBridges · 03-31-2020, 01:36 PM

One way is to ask the user yourself, using MsgBox, maybe like this:

Code:

If File_Already_Exists Then
  OprReply = MsgBox("The Excel workbook " & filename & " already exists.  Do you want to write over" _
    & " it?",vbYesNoCancel + vbQuestion, "Filename conflict!"
  Select Case OprReply
    Case vbYes 'turn off the warning and SaveAs again
    Case vbNo 'cancel the save
    Case vbCancel 'I dunno, whatever you want to do if he hit Cancel
    End Select

In your case, though, that involves detecting ahead of time that the file exists, probably using FileSystemObject. Simpler, in my opinion, to tell VBA to spot the error when it happens, like this: 1) Before you get to this part of the program—I always do it first thing in a module—set error trapping like this:

Code:

On Error GoTo ErrHandle

That tells VBA that when there's an error—any error—instead of handling it in the normal way, VBA should go to the statement label you name. (I always call it "ErrHandle" but you can name it anything you like.) Then, down near the bottom of the program 2) add the part where you handle the error, like this:

Code:

  Exit Sub

ErrHandle:
  If Err.Number = 1004 Then
    'do whatever you want to do if the user rejected the SaveAs
  Else
    Resume
    End If

You have to add the Exit Sub statement before the label, or your code will fall into these statements after it's finished normally. The Resume statement, if the error wasn't 1004, means in that case go back to the statement that caused the error and do it again, and this time abend in whatever way VBA considers normal for that error.