Workbook_BeforeClose not working when closing application

[jblevins]

In Excel 2010, if I use the close workbook "X", Workbook_BeforeClose works, but if I use the application close button, it does not - why?

 

[bobsan42]

Maybe you have disabled events in your code? Hard to tell without seeing the code. Just guessing.
However you can still call the routine ino your code before closing the application.

 

[jblevins]

Maybe you have disabled events in your code? Hard to tell without seeing the code. Just guessing.
However you can still call the routine ino your code before closing the application.

The only line of code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    MsgBox "test"
End Sub

I did create a macro that does the trick, but I would like to have things working like the should.

 

[bobsan42]

Not this code. The one that is closing the application.
Or any other code that calls it ...

 

[jblevins]

The conclusion I have come up with is that the issue is only with .xlsb, all others work as they should.

 

[bobsan42]

Well I played with it a while. Your conclusion I think is not correct. The Workbook type is irrelevant.
case 1. If you use Workbook close event to close the workbook The Before close event will trigger every time w/o fail.

case 2. If you are closing the Application (by Application.Quit or by the X close button) - BeforeClose event will only work once. If you cancel the closing process and then you try again to quit the application the event will not be triggered (although Workbook Close will still trigger it)

Tested on Excel 2016 32 bit, Windows 7 64 bit.
I am pretty sure the behavior will be similar on most if not all systems.
And I believe things are working normally although I may not always understand the mysterious ways ...

 

[jblevins]

What I said is how 2010 works after spending several hours testing.

Let me ask this, did you share the .xlsb and hide it as personal.xlsb is normally used?

 

[bobsan42]

No I haven't but this is new information which you didn't share before, and is not the general case.

It seems your conclusion is partially correct but it is only true if the file is called PERSONAL.XLSB. Fortunately this is not the only filename that will do what you need.

Any other file name (e.g. Personal2.xlsb) or type (e.g. Personal.xlsm) will behave as normally expected. Even add-ins behave in the normal way.


The only thing I can think of is that this is the behavior MS chose for this specific file.