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

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


"Dean Meyer" <[email protected]> wrote in message
news:[email protected]...
> 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)
>

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



Bob Umlas wrote:
> 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
>
>
> "Dean Meyer" <[email protected]> wrote in message
> news:[email protected]...
> > 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)
> >