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

Losing Cell Protection When Saving Workbook
 

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

Losing Cell Protection When Saving Workbook
 

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

Losing Cell Protection When Saving Workbook
 

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

Losing Cell Protection When Saving Workbook
 

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

Losing Cell Protection When Saving Workbook
 

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