Protect a sheet

[Ken Hudson]

I am using the following code to lock cells and protect a sheet. When I
re-open the saved workbook, I am not prompted for a password (dash) when I
unprotect it. What is wrong with my code?
TIA
........................
Range("B7,C7,D7,E7,F7,G7,B8,C8,F8,G8,G55,G94,G95,G144").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.Protect ("dash")
ActiveSheet.EnableSelection = xlUnlockedCells
ActiveWorkbook.SaveAs Filename:="Test", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
ActiveWorkbook.Close False
.........................

 

[Dave Peterson]

My first guess is that you're not opening the correct Test.xls file.

I'd test it by specifying the complete path, like:
ActiveWorkbook.SaveAs Filename:="c:\Test", .....

or add a message box after the save to double check the location.

msgbox activeworkbook.fullname

 

[john]

think this may do what you want.

Sub Test()

With ActiveWorkbook

With .ActiveSheet

With .Range("B7,C7,D7,E7,F7,G7,B8,C8,F8,G8,G55,G94,G95,G144")
.Locked = True
.FormulaHidden = False
End With


.Protect Password:="dash", DrawingObjects:=True, Contents:=True, _
Scenarios:=True

.EnableSelection = xlUnlockedCells

End With

.SaveAs Filename:="C:\Test", FileFormat _
:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
.Close False
End With
End Sub

 

[Ken Hudson]

Thanks John.
Looks like the problem with my code was that the "ActiveSheet.Protect
("dash")" code should not have been coded on its own line. It needed to be
imbedded in the line above:

ActiveSheet.Protect Password:="dash", DrawingObjects:=True, Contents:=True,
Scenarios:=True