Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
protection on excel 2003 | Excel Discussion (Misc queries) | |||
PROTECTION Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003: Protection | Excel Discussion (Misc queries) | |||
Excel 2003: Protection | Excel Discussion (Misc queries) | |||
protection in excel 2003 | Excel Discussion (Misc queries) |