VBA code to automatically respond Yes to a built in dialog box


  • I have a command button macro that saves my Excel workbook to 3 different drives.  Each time I click the button, the file is saved with no problem, but I get a dialog box asking me if I want to overwrite the file.  What code do I need to add to my macro that will automatically answer 'Yes' to the dialog box?  Here is my code (Very simple...I recorded the macro)  The Record Macro feature does not record the interaction with the dialog box.  Thanks

    Private Sub CommandButton1_Click()

    ' Save File

    '   My Passport
        ChDir "E:\"
        ActiveWorkbook.SaveAs Filename:="E:\Budget Worksheet 2016.xlsm", FileFormat _
            :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    '   One Drive
        ChDir "C:\Users\Owner\OneDrive\2016\Finances\Accounting"
        ActiveWorkbook.SaveAs Filename:= _
            " Worksheet 2016.xlsm" _
            , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    '   2016 Finances
        ChDir _
            "C:\Users\Owner\Documents\Desktop My Filing Cabinet\Robert Desktop\2016\Finances\Accounting"
        ActiveWorkbook.SaveAs Filename:= _
            "C:\Users\Owner\Documents\Desktop My Filing Cabinet\Robert Desktop\2016\Finances\Accounting\Budget Worksheet 2016.xlsm" _
            , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    End Sub
    • Moved by litdev Thursday, January 21, 2016 6:41 PM
    Thursday, January 21, 2016 4:39 AM


All replies