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: 

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Written by Dan Blum...stolen from my VBA 2003 book on page 110
'It fools excel into thinking that the workbook has already been saved (since any changes were made)
'  This prevents excel from asking if the user wants the Personal WB saved.

ThisWorkbook.Saved = True
End Sub
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:

  ThisWorkbook.Save = True

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.