CODEWITHSUNDEEP
excelvba
Javier
Javier

Reputation: 309

Strange behavior when closing a workbook twice

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

Upvotes: 1

Views: 209

Answers (2)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

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.

Upvotes: 3

Tim Williams
Tim Williams

Reputation: 166306

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

Upvotes: 0

Related Questions