Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
password protection via code
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
password protection via code
Hi,
works allright for me here and now. Of course, select Case Left(ActiveWorkbook.ActiveSheet.Name, 2) Case "PR" is case sensitive. -- Helmut Weber Win XP, Office 2003 "red.sys" & Chr$(64) & "t-online.de" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
password protection via code
It looks like you want to loop through the worksheets--but your code is always
looking at the activesheet. Option Explicit Sub pword() Dim I As Long Dim ii As Long Dim iSheets As Long Dim NN As String iSheets = ActiveWorkbook.Sheets.Count For ii = 1 To iSheets - 1 ' I don't want the last sheet protected here NN = "" With Sheets(ii) If .ProtectContents _ Or .ProtectDrawingObjects _ Or .ProtectScenarios Then 'already protected Else Select Case UCase(Left(.Name, 2)) Case "PR" NN = "jenn" Case "CF" NN = "riki" Case "SD" NN = "tric" Case "LD" NN = "luda" End Select End If If NN = "" Then 'not in the list or already protected. Else .Protect Password:=NN, _ DrawingObjects:=True, Contents:=True, _ Scenarios:=True End If End With Next ii End Sub swatsp0p wrote: 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. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
password protection via code
And I missed the part where you were activating the sheet. But checking the
protection is different from what you used, too. Dave Peterson wrote: It looks like you want to loop through the worksheets--but your code is always looking at the activesheet. Option Explicit Sub pword() Dim I As Long Dim ii As Long Dim iSheets As Long Dim NN As String iSheets = ActiveWorkbook.Sheets.Count For ii = 1 To iSheets - 1 ' I don't want the last sheet protected here NN = "" With Sheets(ii) If .ProtectContents _ Or .ProtectDrawingObjects _ Or .ProtectScenarios Then 'already protected Else Select Case UCase(Left(.Name, 2)) Case "PR" NN = "jenn" Case "CF" NN = "riki" Case "SD" NN = "tric" Case "LD" NN = "luda" End Select End If If NN = "" Then 'not in the list or already protected. Else .Protect Password:=NN, _ DrawingObjects:=True, Contents:=True, _ Scenarios:=True End If End With Next ii End Sub swatsp0p wrote: 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. -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
password protection via code
Thanks, Dave. Not sure how I missed that. I appreciate your help (as always).
Thanks to Helmut, too. -- Bruce "Dave Peterson" wrote: It looks like you want to loop through the worksheets--but your code is always looking at the activesheet. Option Explicit Sub pword() Dim I As Long Dim ii As Long Dim iSheets As Long Dim NN As String iSheets = ActiveWorkbook.Sheets.Count For ii = 1 To iSheets - 1 ' I don't want the last sheet protected here NN = "" With Sheets(ii) If .ProtectContents _ Or .ProtectDrawingObjects _ Or .ProtectScenarios Then 'already protected Else Select Case UCase(Left(.Name, 2)) Case "PR" NN = "jenn" Case "CF" NN = "riki" Case "SD" NN = "tric" Case "LD" NN = "luda" End Select End If If NN = "" Then 'not in the list or already protected. Else .Protect Password:=NN, _ DrawingObjects:=True, Contents:=True, _ Scenarios:=True End If End With Next ii End Sub swatsp0p wrote: 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. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
password protection via code
Whatever it was, it works now. I'm guessing the checking for protection
gummed up the works. Thanks again. -- The older I get, the better I used to be. "Dave Peterson" wrote: And I missed the part where you were activating the sheet. But checking the protection is different from what you used, too. Dave Peterson wrote: It looks like you want to loop through the worksheets--but your code is always looking at the activesheet. Option Explicit Sub pword() Dim I As Long Dim ii As Long Dim iSheets As Long Dim NN As String iSheets = ActiveWorkbook.Sheets.Count For ii = 1 To iSheets - 1 ' I don't want the last sheet protected here NN = "" With Sheets(ii) If .ProtectContents _ Or .ProtectDrawingObjects _ Or .ProtectScenarios Then 'already protected Else Select Case UCase(Left(.Name, 2)) Case "PR" NN = "jenn" Case "CF" NN = "riki" Case "SD" NN = "tric" Case "LD" NN = "luda" End Select End If If NN = "" Then 'not in the list or already protected. Else .Protect Password:=NN, _ DrawingObjects:=True, Contents:=True, _ Scenarios:=True End If End With Next ii End Sub swatsp0p wrote: 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. -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
password protection via code
It was my mistake that I missed it. But glad you got it working.
swatsp0p wrote: Thanks, Dave. Not sure how I missed that. I appreciate your help (as always). Thanks to Helmut, too. -- Bruce "Dave Peterson" wrote: It looks like you want to loop through the worksheets--but your code is always looking at the activesheet. Option Explicit Sub pword() Dim I As Long Dim ii As Long Dim iSheets As Long Dim NN As String iSheets = ActiveWorkbook.Sheets.Count For ii = 1 To iSheets - 1 ' I don't want the last sheet protected here NN = "" With Sheets(ii) If .ProtectContents _ Or .ProtectDrawingObjects _ Or .ProtectScenarios Then 'already protected Else Select Case UCase(Left(.Name, 2)) Case "PR" NN = "jenn" Case "CF" NN = "riki" Case "SD" NN = "tric" Case "LD" NN = "luda" End Select End If If NN = "" Then 'not in the list or already protected. Else .Protect Password:=NN, _ DrawingObjects:=True, Contents:=True, _ Scenarios:=True End If End With Next ii End Sub swatsp0p wrote: 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. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Password protection in macro ( Anybody can view my password in VB | Excel Discussion (Misc queries) | |||
Excel Data Protection- AKA: Sheet/Macro Password Protection | Setting up and Configuration of Excel | |||
PASSWORD PROTECTION | Excel Worksheet Functions | |||
Put Password Protection in Code | Excel Discussion (Misc queries) | |||
VBA code delete code but ask for password and unlock VBA protection | Excel Programming |