Worksheet Protection

[Guest]

I have a template worksheet that I am trying to use a macro to set passwords
to protect:
1) the workbook level to allow only employee access
2) the worksheet level to protect formulas from being overwritten and limit
access to myself

The attached macro saves the template as a separate filename and tries to
accomplish the objectives listed above. But when I go into the saved
worksheet, only the workbook is protected. The worksheet isn't. What am I
doing wrong?

Sheets("Selections1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, Password:="PASSWORD1"
Sheets("Selections2").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, Password:="PASSWORD1"
myFileName = Sheet8.Range("D2").Value & Sheet8.Range("B2").Value
ActiveWorkbook.SaveAs Filename:="V:\Selections\Jobs\" & myFileName &
".xls", _
FileFormat:=xlNormal, Password:="PASSWORD2", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=True

 

[Gareth]

It *looks* like it should work.... but maybe you're protecting sheets on
a different workbook from the workbook you are protecting. Try and be
more explicit in your code. I've modified your code slightly, by using
the With statement and the "." we can be certain that we're working on
the same workbook.

With Activeworkbook

.Sheets("Selections1").Protect DrawingObjects:=True, _

Contents:=True, _
Scenarios:=True, _
Password:="PASSWORD1"
.Sheets("Selections2").Protect DrawingObjects:=True, _

Contents:=True, _
Scenarios:=True, _
Password:="PASSWORD1"

myFileName = .Sheet8.Range("D2").Value & .Sheet8.Range("B2").Value
.SaveAs Filename:="V:\Selections\Jobs\" & myFileName & ".xls", _
FileFormat:=xlNormal, _
Password:="PASSWORD2", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=True

End with

HTH,
Gareth