03-31-2020, 01:36 PM
|
 |
Expert
|
|
Join Date: May 2013
Location: USA
Posts: 700
|
|
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.
|
Similar Threads
How to Control Worksheet Event Handler in Module?
Windows 7 64bit 
Office 2010 32bit
Threaded Mode