Workbook_BeforeClose Event Procedure

**DanBlum** · 04-19-2012, 04:39 PM

I have written an event procedure that is intended to prevent Excel from asking whether the user wants the Personal workbook to be saved. The macros that are called in the Personal WB's auto-open macro store intermediate results in the Personal WB worksheets so that Excel always finds changes have been made and therefore asks the user if it is to be saved before closing. However the stored results are just that...intermediate....and not needed. The Personal workbook never needs to be saved by the user. I want to prevent Excel from bothering the user in this case.

I found in my Excel 2003 VBA Programmers Reference (Paul T. Kimmel, etal.) the following event procedure:

Please Login or Register  to view this content.

The code is placed in the ThisWorkbook module of the Personal WB.

However, it is not working all the time. I believe it is not working because it is not invoked under certain "closing circumstances". I suspect this because I have placed a breakpoint at the line:

Please Login or Register  to view this content.

It seems to work when I explicitly do a File-->Close command against the Personal WB. It works, and as anticipated it hits the breakpoint. When directed to continue the command works and the WB is closed with no further ado!

However if I just click the Windows close (X) button for the window, the event procedure seems not to run. Excel proceeds to ask if the application workbook is to be saved (which I expect), but then proceeds to do the same for the Personal WB. The breakpoint is not hit, and the Excel dialog asking the user whether to save the Personal workbook is (undesirably) displayed.

The Personal WB runs as hidden and no one is going to do an explicit File-> Close of the WB. They will always just click the Window's X. ....leaving me where I started.

Is this a limitation of the event procedure design? It seems Windows does return control to Excel because Excel properly asks the user about saving the Application WB. However, my environment is behaving as if Excel doesn't invoke the Workbook_BeforeClose event procedure.

Other facts: Vista, Excel 2010

Any help would be appreciated.

**JBeaucaire** · 04-19-2012, 04:54 PM

If you have a macro in your personal.xlsb that is storing data IN that file, edit THAT macro to save the personal file during that macro rather than in a separate event.

**DanBlum** · 04-20-2012, 07:47 AM

Thanks for the quick and thoughtful reply. It's the "keep it simple" thought. Your thought generated a thought of mine too: I don't need to incur the overhead of doing a save at all. I can still just do the

Please Login or Register  to view this content.

at the point I store into the workbook. ... because, it wasn't that the

ThisWorkbook.Save = True

didn't work, it was was that it wasn't getting executed.

Thanks again.