Protect Worksheet / Workbook in BeforeSave Event

[tc69]

I was unprotecting and reprotecting all Worksheets and the Workbook in a
Workbook.BeforeSave Event and it had been working fine for weeks. Now all of
a sudden it won't work. It still works from normal Sub's and even from
inside the Workbook.BeforeClose Event. I can't find any changes to the
BeforeSave Event that would account for this. I tried stripping my
BeforeSave Event down to just doing an explicit Protect on a single Worksheet
by index with a literal Password and it still doesn't work. I tried it in a
brand new Workbook's BeforeSave Event and it still works. I'm using XL 2k on
XP Pro SP2. Thanks!

 

[Barb Reinhardt]

I wonder if you used

Application.EnableEvents = FALSE

and then didn't turn events back on with

Application.EnableEvents = TRUE

at some time before you save.

 

[tc69]

Nope, Application.EnableEvents was True. I stepped right throught the
Protect call and watched it not change the Worksheet's ProtectContents
Property AND not set an error!?!

Doh! I just realized what changed that made it stopped working. I created
a Macro that emulates the Save option so I could do stuff after the Save
since there's no Workbook.AfterSave Event. Apparently, when
Workbook.BeforeSave is called due to a Workbook.Save call vs. directly from
the predefined Save Control, Protect won't work and won't generate an error.
I actually had a similar problem with calling Workbook./Worksheet. Unprotect
and Workbook.Save from Workbook.BeforeClose if the Event was raised due to a
Workbook.Close call vs. directly from the predefined Close Control.

I think I read some post about certain XL menu commands (or their
corresponding VBA methods) not working from inside certain Events, but I
can't find it.

Anyone know about this problem?