Losing Cell Protection When Saving Workbook

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.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

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.



Chaplain Doug wrote:
>
> 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.
> --
> Dr. Doug Pruiett
> Good News Jail & Prison Ministry
> www.goodnewsjail.org

--

Dave Peterson

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?
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org


"Dave Peterson" wrote:

> 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.
>
>
>
> Chaplain Doug wrote:
> >
> > 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.
> > --
> > Dr. Doug Pruiett
> > Good News Jail & Prison Ministry
> > www.goodnewsjail.org
>
> --
>
> Dave Peterson
>

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?
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org


"Dave Peterson" wrote:

> 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.
>
>
>
> Chaplain Doug wrote:
> >
> > 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.
> > --
> > Dr. Doug Pruiett
> > Good News Jail & Prison Ministry
> > www.goodnewsjail.org
>
> --
>
> 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.

Chaplain Doug wrote:
>
> 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?
> --
> Dr. Doug Pruiett
> Good News Jail & Prison Ministry
> www.goodnewsjail.org
>
> "Dave Peterson" wrote:
>
> > 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.
> >
> >
> >
> > Chaplain Doug wrote:
> > >
> > > 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.
> > > --
> > > Dr. Doug Pruiett
> > > Good News Jail & Prison Ministry
> > > www.goodnewsjail.org
> >
> > --
> >
> > Dave Peterson
> >

--

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).

Chaplain Doug wrote:
>
> 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?
> --
> Dr. Doug Pruiett
> Good News Jail & Prison Ministry
> www.goodnewsjail.org
>
> "Dave Peterson" wrote:
>
> > 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.
> >
> >
> >
> > Chaplain Doug wrote:
> > >
> > > 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.
> > > --
> > > Dr. Doug Pruiett
> > > Good News Jail & Prison Ministry
> > > www.goodnewsjail.org
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson