Excel 2003 changes protection when saving
 

I have an Excel workbook with 9 worksheets on which I run a number of macros
saved with the workbook (not external). These are run in response to a
button click.

I have been applying protection to all the sheets in this workbook since it
will be used by people that don't know what they are doing in Excel. When
applying protection I only select (check) the 'Select Unlocked Cells' option
after which everything is properly locked up except for the unlocked cells.

Since the worksheets are protected so that the user can only access the
unlocked cells the macros must unlock the sheets to perform some of the
functions and then relock the sheets before the user gets control again.

The code is generally as follows...

Sheets("1st Quarter").Select
ActiveSheet.Unprotect
....
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Everything works properly during any given session. That is: After running
the macros the appropriate cells are locked and the unlocked cells are still
accessable.

However, after saving the file then reopening it the protection now has the
'Select Locked Cells' option selected as well.

If I set the protection by hand and save the workbook, close it and reopen
it everything is fine. If I make some changes that do not involve running
the macros, save, unload and reload everything is still fine.

Something happens after running the macros (any of them that unprotect then
reprotect a sheet) that causes Excel to behave correctly during the session
but to change the protection on all pages that had protection turned off/on
by the macros.

Any help on this would be greatly appreciated! TIA

Mark

Excel 2003 changes protection when saving
 

No takers on this one?

Well it seems to be a bug in Excel 2003, and it is reproduceable! Since the
problem only appears when reloading a saved sheet after it has had
protection turned off and on again the workaround was to install an
Auto_Open macro that that iterates through the worksheets and resets the
security before the user can begin working with the workbook. The following
code does the trick...

Dim oWS As Worksheet

' Block Screen Updates
Application.ScreenUpdating = False


For Each oWS In Worksheets

If InStr(1, oWS.Name, "Quarter", vbBinaryCompare) Then

' Select the sheet
Sheets(oWS.Name).Select

' Reset protection on the Quarter Sheets to fix bug in Excel
2003
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

End If

Next ' oWS

' Open with the first sheet
Sheets("1st Quarter").Select

' Restore Screen Updates
Application.ScreenUpdating = True

Excel 2003 changes protection when saving
 

It looks like a bug to me, too.

But in earlier versions of excel (pre xl2002), this was a setting that had to be
set via the VBE (either manually in the properties window or via code).

It looks like MS missed it for code.

I think your workaround is the way to go.

Ps. I believe this has come up a couple of times in the newsgroups.

Mark Alsop wrote:

No takers on this one?

Well it seems to be a bug in Excel 2003, and it is reproduceable! Since the
problem only appears when reloading a saved sheet after it has had
protection turned off and on again the workaround was to install an
Auto_Open macro that that iterates through the worksheets and resets the
security before the user can begin working with the workbook. The following
code does the trick...

Dim oWS As Worksheet

' Block Screen Updates
Application.ScreenUpdating = False

For Each oWS In Worksheets

If InStr(1, oWS.Name, "Quarter", vbBinaryCompare) Then

' Select the sheet
Sheets(oWS.Name).Select

' Reset protection on the Quarter Sheets to fix bug in Excel
2003
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

End If

Next ' oWS

' Open with the first sheet
Sheets("1st Quarter").Select

' Restore Screen Updates
Application.ScreenUpdating = True

--

Dave Peterson

Excel 2003 changes protection when saving
 

Dave,

Quote: Ps. I believe this has come up a couple of times in the newsgroups.

I'm sure you are right; I only searched the more recent messages. Thanks for
the reply! I teach at a very small private high school so I wear too many
hats to do anything as well as I would like! These newsgroups are a Godsend
for the likes of me but often I end up doing it myself anyway.

Mark