Weird behaviour of the BeforeSave procedure when fired by Me.Save

**mach128x** · 04-28-2011, 02:22 PM

Hi there fellow Excel users,

I'm pretty new to VBA (but studied C++ and other languages).

I've come accross a problem with a BeforeSave procedure not executing properly when fired by Me.Save as part of a BeforeClose procedure.

Consider the piece of code below.

When manually saving (through Ctrl+S), the Workbook_BeforeSave event is fired and everything works as expected.

However, firing the BeforeClose event does not bring the expected result. The "Me.Save" line in the BeforeClose effectively fires the Workbook_BeforeSave event, as confirmed by the MsgBox that pop ups, but the two ThisWorkbook.Worksheets().Visible lines are not getting executed.

I don't quite understand why it's acting like this, as the code seem pretty simple and logical to me. Is there some special restriction that I wouldn't know about event procedures that would prevent this piece of code from working properly?

Please Login or Register  to view this content.

**Colin Legg** · 04-28-2011, 03:32 PM

Hi and welcome to the forum.

Yes, this is a restriction - basically the Workbook class's methods are blocked once the Workbook.Save call from the Workbook_BeforeClose event handler is made. We had a good chat about what might cause this behaviour here and the best idea we had was that it is designed to prevent infinite recursion. As I mentioned on that thread, I was never 100% comfortable with that theory because the same restriction isn't applied in, for example, MS Word.

To workaround it you can temporarily disable events in the Workbook_BeforeClose event handler (to prevent Workbook_BeforeSave being called at al) and hide the sheets within the Workbook_BeforeClose event handler.