Tuesday 3 March 2015

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.

5 comments:

  1. Deu certo Obrigado

    ReplyDelete
  2. karthikbyggari18 May 2015 at 03:13

    You are welcome! (De nada!)

    ReplyDelete
  3. Hi
    I used this code, but still displays alert. What should I do?

    Also I want to discuss one point with you. If you are not busy can you help me out. I have created an Excel file with about 40 macros. I have used WMP control also. But as soon as I open the application both these files start to run. For the time being I have mute the sound. But I want them to start when I press the start button. Can you help me out, Please.

    Regards,

    ReplyDelete
    Replies
    1. In the workbookopen event you can control what to do when you open the workbook.
      Please check this workbookopen event for more insights.
      Thank you.

      Delete
  4. Your blog is very nice and provide me informative content thanks for sharing for more information about Open Close Workbook

    ReplyDelete

 
biz.