Losing Cell Protection When Saving Workbook

[Guest]

Excel 2003. I programmatically protect each sheet in a workbook as I
programmatically create the sheets. Moreover, I enable only the unlocked
cells in the sheets to be selected. I use:

WkSh.Protect
WkSh.EnableSelection = xlUnlockedCells

When I put breakpoints in while debugging I find that the protection is
working as expected (the sheet is protected and I can only select unlocked
cells).

When I am finished creating all the sheets, I then programmatically protect
the whole work book as:

WbNew.Protect

When I check the sheets after this operation, they still behave as desired.
HOWEVER, when I save the workbook using:

WbNew.Close SaveChanges:=True

and then open the workbook, I find the workbook protected (as expected), the
sheets protected (as expected), but I can select the locked cells in the
sheets

What could be causing this? Thanks for the help.

 

[Dave Peterson]

This is one of those settings that excel doesn't remember when the file is
closed, then reopened.

That was what this warning meant:

Excel doesn't remember this setting--so the code uses the auto_open procedure to
set it each time the workbook opens.

 

[Guest]

Dear Dave:

Not sure what you mean by "uses the auto_open procedure to set it each time
the workbook opens." How can I do this programmatically as I produce the
worksheet?

 

[Guest]

Dave:

When I manually set the sheet to protect (allowing only access to unlocked
cells) and save the file and reopen, the protection settings are still there.
Why am I losing them only when I set the protection progrmmatically?

 

[Dave Peterson]

There are certain routines that excel will run each time you open the workbook
(if macros are enabled!).

One is named Auto_Open and is stored in a General module.

The other is named Workbook_Open and is stored in the ThisWorkbook module.

The code that you got the other day uses a routine named Auto_Open.

Dear Dave:

Not sure what you mean by "uses the auto_open procedure to set it each time
the workbook opens." How can I do this programmatically as I produce the
worksheet?

 

[Dave Peterson]

Not all of the protection settings are forgotten when the workbook is closed and
reopened. But there are a few--the .enableselection, .enableautofilter,
..enableoutlining are a few that come to mind.

And these protection settings will be forgotten if you do it manually (if
available) or via code (if I remember correctly).

Dave:

When I manually set the sheet to protect (allowing only access to unlocked
cells) and save the file and reopen, the protection settings are still there.
Why am I losing them only when I set the protection progrmmatically?