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

ChrisOK · 03-30-2020, 03:59 PM

How do I add the simplest method for On Error handling to STEP 6 when the user decides they do NOT want to accept the overwriting of the existing file?

(A dialog box prompts user:
"A file named 'xxxxxx.xlsx" already exists in this location. Do you want to replace it?)

OUTCOME:

If User clicks "Yes", works great, script continues to end

If clicks "No" or "Cancel" - (Run-time error '1004' Method 'SaveAs' of object '_Workbook' failed

I've reviewed many posts but nothing with simple capability to toggle out of it and end the sub if user chooses "No" or "Cancel" as I did while testing...

Optimally, something like this would be great:
If Cancel (END SUB)
If No (prompt user with field to rename file)

Code:

Sub Sample()



'5-GIVES NEWLY CREATED SHEET A NAME
        ActiveSheet.Name = "NewTabName"


'6-SAVE RESULTS FILE TO DESKTOP
    
        ChDir "C:\Users\johndoe\Desktop"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\johndoe\Desktop\NewFileFolderListName.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False


'7-GENERATE A COUNT OF DUP FINDINGS

    Const CountIFColumn As String = "D1:D500"
    Dim f As Integer
    f = WorksheetFunction.CountIf(Range(CountIFColumn), "TRUE")
    MsgBox f, vbInformation, "Count of Duplicates Found"
    
    
 '8-PROMPT USER TO NAME NEW SHEET CREATED WITH USER-ENTRY FIELD:
     'PROMPTS USER TO NAME THE SHEET
    
    Dim NewName As String
    NewName = InputBox("What Do You Want to Name This Sheet ?")
    Sheets("NewTabName").Name = NewName



End Sub