View Single Post
 
Old 03-30-2020, 03:59 PM
ChrisOK ChrisOK is offline Windows 10 Office 2019
Advanced Beginner
 
Join Date: Sep 2016
Posts: 51
ChrisOK is on a distinguished road
Question Error Handler If User Does Not Want to Overwrite File

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
Reply With Quote