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 procedu

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

If ImSaving Then Exit Sub
....
....
Bob Umlas
Excel MVP


"Dean Meyer" wrote in message
ps.com...
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 procedu

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

If ImSaving Then Exit Sub
...
...
Bob Umlas
Excel MVP


"Dean Meyer" wrote in message
ps.com...
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)