Workbook_BeforeClose not working when closing application

jblevins

Board Regular
Joined
Sep 2, 2013
Messages
192
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?
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
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

Board Regular
Joined
Sep 2, 2013
Messages
192
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:
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.
 
Last edited:

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
Not this code. The one that is closing the application.
Or any other code that calls it ...
 

jblevins

Board Regular
Joined
Sep 2, 2013
Messages
192

ADVERTISEMENT

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

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
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 ...
 
Last edited:

jblevins

Board Regular
Joined
Sep 2, 2013
Messages
192
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?
 
Last edited:

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,768
Messages
5,524,787
Members
409,600
Latest member
Dunnowhatfor

This Week's Hot Topics

Top