Protect a sheet

K

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
.........................
 
D

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
 
J

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
 
K

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top