Strange behavior when closing a workbook twice

1
votes

Strange issue when closing a workbook twice from VBA (Workbook_BeforeClose)

Hi. This problem appears to me in an extremely simple workbook: Workbook_BeforeClose only.

Option Explicit
     Private Sub Workbook_BeforeClose(Cancel As Boolean)
     ThisWorkbook.Close SaveChanges:=False
End Sub

If I open and close the workbook twice, the main Excel screen looks like this, and it's impossible to do something, I can only close it from the status bar:

final status

2
excelvba
So, you closed the workbook and the application itself still exists. – K.Dᴀᴠɪs
What's the aim of this code? – Tim Williams
Last line try Application.Quit. I've noticed behavior in a similar situation before as well. I believe it had something to do with other hidden "workbooks" being open, such as a personal macro workbook that can be seen in the VBE. I noticed this behavior was eliminated by looping all workbooks in the application object and ensuring they were closed, so there's another method you can try. – K.Dᴀᴠɪs
Tim Williams, obviously is a extreme simplification – Javier
K. Davis, I can't close the entire application, users would kill me :) – Javier

2 Answers

3
votes

If all you are trying to do is to not prompt the user to save changes, just play with the appropriate flags to 'trick' Excel that changes have already been saved.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Me.Saved = True

End Sub

This will allow the workbook to close, without prompting any changes to be saved, but this does not actually save them.

Notice the subtle difference between the words: Me.Saved and Me.Save.

  • Saved is a property that gets flipped to False when Excel detects changes were made as of the last save.

  • Save is a method - not a property as above - that actually will save the workbook.

Your workbook is already closing, which is what fired this event to begin with. No need to try to close it again within this event. Just tell Excel that no changes have been made since the last save, and it should close all on it's own - without the prompts.

0
votes

It's possible you're re-triggering the event. Try something like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
     Static InProgress As Boolean

     If InProgress Then Exit Sub

     InProgress = True
     ThisWorkbook.Close SaveChanges:=False
End Sub