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

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

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