Error Handler If User Does Not Want to Overwrite File

ChrisOK · #1 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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
MS Word Error: This file is in use by another application or user	conky	Word	4	02-26-2019 09:21 AM
How can I get Outlook from one user to another w/o a .pst file (primary user on computer is corrupt)	Tenmakk	Outlook	0	03-01-2015 12:49 PM
How to Control Worksheet Event Handler in Module?	tinfanide	Excel Programming	2	10-19-2014 09:46 AM
Stop macro if no file is selected in dialog box (when user presses cancel instead of selecting file)	spencerw.smith	Word VBA	2	08-12-2014 07:56 AM
Word does not ask do i want to overwrite a file on saving a file	sam2149	Word	2	03-24-2014 04:18 AM