Workbook.Save triggers event Workbook_BeforeSave and things don't work!

  • Thread starter Thread starter Dean Meyer
  • Start date Start date
D

Dean Meyer

I catch the event Workbook_BeforeSave and do a number of things, and
that works fine.

But when a VB subroutine calls Workbook.Save or Workbook.SaveAs, which
of course triggers the Workbook_BeforeSave event, a number of things in
the BeforeSave sub just don't work. The list includes:
* Worksheet.Unprotect / Worksheet.Protect
'*Application.Iteration = True/False
'*Application.ScreenUpdating = True/False
'*Application.Goto / Range.Select
'*Application.StatusBar = ...

Does anybody have any clues as to why? How about a work-around?

(Excel 2003)
 
You can set a global variable to true before the Save, and test it in the
Event procedure:

ImSaving = True
ActiveWorkbook.Save
ImSaving = False
....
....
In the BeforeSave event:

If ImSaving Then Exit Sub
....
....
Bob Umlas
Excel MVP
 
Thanks, Bob. But I WANT the BeforeSave event code to run, not just
skip it!

Does anyone have any idea why so much VB code fails when a BeforeSave
event is triggered by a Workbook.Save rather than by the user?

--Dean
 
Back
Top