Exit without prompt problem

[gemini528]

Dear Excel Guru

From Excel 2007 I upgraded my Excel to Excel 2019. The VBA code below works fine with my previous Excel but when I upgraded to the new version of Excel when I exit the program it has a message “Want to SAVE your changes…”. On the previous version it close without any prompt or message.

Your help is very much appreciated.

Thank you.

Sub ERRClose()

Dim Backup As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.DisplayFormulaBar = True

    Backup = "D:\ERR FILES\BACKUP\Kaperahan " & Format(Date, "YYYY-MM-DD") & ".xlsb"

    If Len(Dir("D:\", vbDirectory)) = 0 Then 
       Beep
      MsgBox "Please Insert the USB Drive and Try Again!"
    Exit Sub

    End If

    Application.DisplayAlerts = False
      ThisWorkbook.Save
      ThisWorkbook.SaveAs Filename:=Backup
    Application.DisplayAlerts = True
    Application.Quit

      On Error GoTo error_Handler:
error_Handler:
Application.Quit
Application.DisplayAlerts = True

 

[jasonb75]

Try removing the Application.DisplayAlerts = True lines, you only need that line if you're closing the workbook but not quitting the application.

If you have Before_Close code in your workbook then you will probably need to use Application.EnableEvents = False as well.

 

[gemini528]

Thank you for your quick reply.

I removed the all the "Application.DisplayAlerts = True" lines but still the same. What I'm trying to achieve is when I close the file, It will SAVE the File and the BACKUP File without any prompt. It should overwrite the existing file without any prompt then close the Application (Excel Application.Quit). With Excel 2007 it is working fine. Please re-write the code if needed. Thank you very much.

 

[jasonb75]

Do you have any other code in the workbook?

A similar problem came up recently where a Before_Close event was causing the problem.

 

[gemini528]

Do you have any other code in the workbook?

A similar problem came up recently where a Before_Close event was causing the problem.

-----
Yes, Here is the full code...

Thank you for your big help.

Option Explicit
Public myFlg As Boolean
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Backup As String
       Application.ScreenUpdating = False
       Application.EnableEvents = False
       Application.Calculation = xlCalculationAutomatic
       Application.DisplayAlerts = False

  If myFlg = True Then Exit Sub
   MsgBox "Please click the ""Exit and Save"" button on the Main Menu!", vbInformation + vbOKOnly, ""
   Application.DisplayAlerts = False

  Cancel = True

       Application.EnableEvents = True
       Application.ScreenUpdating = True
       Application.DisplayAlerts = True

End Sub

‘ - - - - - - - - - - - - - - - - - - - - - - - -

Sub ERRClose()

Dim Backup As String

myFlg = True

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.DisplayFormulaBar = True

    Backup = "D:\ERR FILES\BACKUP\Kaperahan " & Format(Date, "YYYY-MM-DD") & ".xlsb"

    If Len(Dir("D:\", vbDirectory)) = 0 Then 'Drive "F" for Home and "E" for Office
       Beep
      MsgBox "Please Insert the USB Drive and Try Again!"
     Exit Sub
    End If

    Application.DisplayAlerts = False
      ThisWorkbook.Save
      ThisWorkbook.SaveAs Filename:=Backup
    Application.DisplayAlerts = True
    Application.Quit

      On Error GoTo error_Handler:
error_Handler:
Application.Quit

'Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Quit
End Sub

 

[jasonb75]

Is it happening when you use the 'Exit and Save' button, when you try to exit without using the button, or both?

 

[gemini528]

Is it happening when you use the 'Exit and Save' button, when you try to exit without using the button, or both?

Exit and Save button is the only way to Exit and Save, because when you click the "X" it will not Close and Exit.
Thank you for your help.

 

[gemini528]

By the way, when you click the "Exit and Save Button", the message is "Do you want to Save Changes...." which on my old version it just save and exit Excel.

Thanks.

 

[jasonb75]

I've taken out a lot of code that didn't appear to be doing anything, hopefully this will function as needed.

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = True
    MsgBox "Please click the ""Exit and Save"" button on the Main Menu!", vbInformation + vbOKOnly, ""
End Sub

'- - - - - - - - - - - - - - - - - - - - - - - -

Sub ERRClose()

Dim Backup As String
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .EnableEvents = False
    Backup = "D:\ERR FILES\BACKUP\Kaperahan " & Format(Date, "YYYY-MM-DD") & ".xlsb"

    If Len(Dir("D:\", vbDirectory)) = 0 Then 'Drive "F" for Home and "E" for Office
        Beep
        MsgBox "Please Insert the USB Drive and Try Again!"
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    Else
        ThisWorkbook.Save
        ThisWorkbook.SaveAs Filename:=Backup
        .Quit
    End If
End With
End Sub

 

[gemini528]

I've taken out a lot of code that didn't appear to be doing anything, hopefully this will function as needed.

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = True
    MsgBox "Please click the ""Exit and Save"" button on the Main Menu!", vbInformation + vbOKOnly, ""
End Sub

'- - - - - - - - - - - - - - - - - - - - - - - -

Sub ERRClose()

Dim Backup As String
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .EnableEvents = False
    Backup = "D:\ERR FILES\BACKUP\Kaperahan " & Format(Date, "YYYY-MM-DD") & ".xlsb"

    If Len(Dir("D:\", vbDirectory)) = 0 Then 'Drive "F" for Home and "E" for Office
        Beep
        MsgBox "Please Insert the USB Drive and Try Again!"
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    Else
        ThisWorkbook.Save
        ThisWorkbook.SaveAs Filename:=Backup
        .Quit
    End If
End With
End Sub

Thank you very much for your response. I tested it and it works to my satisfaction. I got those codes in the internet and with your revision it is very short and effective. I am a novice that’s why I don’t have the knowledge of getting it right. I’m glad that there is a forum like these.

I am want to install it on my MacBook Pro but I don’t know how to configure it on the drive. Can you convert the code to run on MacBook Pro? “Backup =:\ERR Files\Backup\....”. My MacBook drive is “McIntosh/ERR Files/Backup?....”

Kudos and thank you very much.