G
Guest
Can anyone tell me where I am going wrong here? I have a multisheet workbook
that will be used by several people. I want to protect each sheet with a
password that will be based on the user. Each user's sheets will have a
similar naming criteria (e.g. Jenn's sheets will be named "PR.....", Riki's
will all be "CF....", etc.). I have code that looks at each sheet's name and
protects each sheet. However, I can't seem to get the password to be
applied. Also, the section that checks for protection isn't working, either
Any ideas? Here is the code I am using. I want to put this in the
Before_Close of the This Workbook...
Sub pword()
Dim I As Integer, ii As Integer, iSheets As Integer
Dim NN As String
iSheets = ActiveWorkbook.Sheets.Count
For ii = 1 To iSheets - 1 ' I don't want the last sheet protected here
Sheets(ii).Activate
If ActiveWorkbook.ActiveSheet.Protect = True Then GoTo skipme
'If sheet is already protected, don't bother
Select Case Left(ActiveWorkbook.ActiveSheet.Name, 2)
Case "PR"
NN = "jenn"
Case "CF"
NN = "riki"
Case "SD"
NN = "tric"
Case "LD"
NN = "luda"
Case Else
GoTo skipme
End Select
ActiveWorkbook.ActiveSheet.Protect Password:=NN, DrawingObjects:=True,
Contents:=True, Scenarios:=True
skipme:
Next ii
End Sub
--
Thanks in advance,
Bruce
swatsp0p
The older I get, the better I used to be.
that will be used by several people. I want to protect each sheet with a
password that will be based on the user. Each user's sheets will have a
similar naming criteria (e.g. Jenn's sheets will be named "PR.....", Riki's
will all be "CF....", etc.). I have code that looks at each sheet's name and
protects each sheet. However, I can't seem to get the password to be
applied. Also, the section that checks for protection isn't working, either
Any ideas? Here is the code I am using. I want to put this in the
Before_Close of the This Workbook...
Sub pword()
Dim I As Integer, ii As Integer, iSheets As Integer
Dim NN As String
iSheets = ActiveWorkbook.Sheets.Count
For ii = 1 To iSheets - 1 ' I don't want the last sheet protected here
Sheets(ii).Activate
If ActiveWorkbook.ActiveSheet.Protect = True Then GoTo skipme
'If sheet is already protected, don't bother
Select Case Left(ActiveWorkbook.ActiveSheet.Name, 2)
Case "PR"
NN = "jenn"
Case "CF"
NN = "riki"
Case "SD"
NN = "tric"
Case "LD"
NN = "luda"
Case Else
GoTo skipme
End Select
ActiveWorkbook.ActiveSheet.Protect Password:=NN, DrawingObjects:=True,
Contents:=True, Scenarios:=True
skipme:
Next ii
End Sub
--
Thanks in advance,
Bruce
swatsp0p
The older I get, the better I used to be.