Lost Protection Settings on Save

[Paul Urey]

Hi,

I have created a worksheet (Excel 2002) that is protected
with a password, with protection settings to allow users
to 'Select unlocked cells' only. There is a macro which
unprotects the sheet, changes what cells are protected
and what isn't, then reprotects the sheet (see macro code
below).

The problem I have is that if you save the worksheet
after running this macro and exit Excel (before the save
the worksheet protection is correct), then reopen the
worksheet, the protection settings have been lost,
allowing users to select both locked and unlocked cells.
However, if after running the macro the sheet is manually
unprotected and then manually protected (without changing
protection settings - dialog box shows users can
only 'Select unlocked cells') then save the worksheet and
exit Excel. Reloading the worksheet then has the correct
protection settings (i.e. users can only select unlocked
cells).



**********************************************

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/06/2003 by Paul Urey
'

'
Range("B1010").Select
ActiveSheet.Unprotect Password:="roger"
Selection.Locked = True
Selection.FormulaHidden = False
Range("B1212").Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("B1414").Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("E12:G12").Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("H12:I12").Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("H14:I14").Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("J12:M12").Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("J14:M14").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveWindow.SmallScroll Down:=9
Range("E23:L70").Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("M23:M70").Select
Selection.Locked = False
Selection.FormulaHidden = False
ActiveWindow.SmallScroll Down:=54
Range("E71:F78").Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("G72:G78").Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("H72:I74").Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("H76:I78").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("K71").Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("K76").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Shapes("CommandButton1").Select
Selection.Enabled = "False"
ActiveWindow.SmallScroll Down:=-60
ActiveSheet.Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True, Password:="roger"
ActiveWindow.SmallScroll Down:=-45
End Sub
*****************************************************

I would appreciate any help.

Thanks in advance
Paul.

 

[Andrew Lenczycki]

Paul,

Are you re-protecting the file before you leave (close)
the file? This can be done by adding a procedure called
Auto_close to a VBA module. Put the code to re-protect
here. This procedure will automatically execute each
time just before the file closes.

Andrew Lenczycki

 

[steve]

Paul,

You might want to put this in the ThisWorkbook module

Private Sub Workbook_Open()
Dim x as Integer
For x = 1 to ActiveWorkbook.Worksheets.Count
Sheets(x).EnableSelection = xlUnlockedCells
Next
End Sub

This way whenever the workbook is open and IF Macros are Enabled
you should get what you want.

steve