How to suppress "Save Changes" prompt when the excel workbook is closed - VBA

Welcome to Logically Proven blog.

This post demonstrates how to suppress "Save Changes" prompt when you close the excel workbook using VBA (Visual Basic for Applications).

Note - This code works only when macros are enabled.

Sometimes it is required to suppress "Save Changes" Prompt in your excel application. It will be so annoying every time when you try to close the workbook the prompt appears.

Let get into the details.

When you suppress the prompt, you have the following two choices to decide -

1. Save the changes and Close the workbook
2. Close the workbook without saving the changes

We are achieving this functionality in the Workbook_BeforeClose() event. This event triggers by the excel application when you close the excel workbook.

Save the changes and Close the workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    If Application.Workbooks.Count = 1 Then
        Application.DisplayAlerts = False
        ThisWorkbook.Save
        Application.Quit
    Else
        ThisWorkbook.Close savechanges:=True
    End If
 
End Sub

In this code, if open workbooks count is one, we are saving the workbook and quitting the excel application. If open workbooks count is more than one we are saving the currently working excel workbook and closing the workbook. The other excel workbooks remains open.

Close the workbook without saving the changes:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    If Application.Workbooks.Count = 1 Then
        Application.DisplayAlerts = False
        Application.Quit
    Else
        ThisWorkbook.Close savechanges:=False
    End If
 
End Sub

This code quits the excel application if open workbooks count is one. Else it closes only the current working excel workbook without saving the changes.

In this event, you can perform some other tasks before closing your excel application. For example clearing data, setting default values etc.

Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.

Logically Proven
Learn, Teach, Share

Karthik Byggari

Author & Editor

Computer Science graduate, Techie, Founder of logicallyproven, Love to Share and Read About pprogramming related things.