View Single Post
Old 03-31-2020, 01:36 PM
BobBridges's Avatar
BobBridges BobBridges is offline Windows 7 64bit Office 2010 32bit
Join Date: May 2013
Location: USA
Posts: 675
BobBridges will become famous soon enoughBobBridges will become famous soon enough

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

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:

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:

  Exit Sub

  If Err.Number = 1004 Then
    'do whatever you want to do if the user rejected the SaveAs
    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.
Reply With Quote