protecting multi shts with separate pwrds

[jiddings]

I have a list of passwords that I want to apply individually to mulitple worksheets. How would I implement this in VBA?

Also, I was trying to look if such an applicable article was in the VBAX knowledge base. However, I'm unable to get beyond the first listing and when I select Excel only for a filter this doesn't work either.

Any help on these problems would be appreciated.
Jack

[Simon Lloyd]

Something like this in your Thisworkbook module!:
[VBA]Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Select Case Sh.Name
Case Is = "Sheet1"
ActiveSheet.Protect Password:="password1"
Case Is = "Sheet2"
ActiveSheet.Protect Password:="password2"
Case Is = "Sheet3"
ActiveSheet.Protect Password:="password3"
End Select
End Sub
[/VBA]

[mdmackillop]

Also, I was trying to look if such an applicable article was in the VBAX knowledge base. However, I'm unable to get beyond the first listing and when I select Excel only for a filter this doesn't work either.

Use the attachment in the first post here meantime.

[jiddings]

Simon,
Thanks for your reply.
Sorry, I wasn't as clear as I should have been on my initial post.
What I have is a separate listing in a password spreadsheet (separate from the one I am applying the passwords to the individual sheets) of the worksheet name and password applicable to each worksheet.
What I want to do is read the password file and apply and update as necessary the spreadsheet with the passwords as they continue to develop / change. My thinking is to loop thru the the listing and apply the passwords.
Any thoughts on how I can accomplish this task ?
Jack

[Simon Lloyd]

Well, in which column is the worksheet name held and in which column is the password held?

[Simon Lloyd]

Assuming you sheet name is in column A and password in column B you can still use the the sheet activate
[VBA]

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim ShPassword As String
ShPassword = Application.WorksheetFunction.VLookup(ActiveSheet.Name, Sheets("Passwords") _
.Range("A1:B" & Sheets("Passwords").Range("B" & Rows.Count).End(xlUp).Row), 2, False)
ActiveSheet.Protect Password:=ShPassword
End Sub

[/VBA]

[jiddings]

Thanks Simon,
That works with a worksheet in the same workbook.
How do I modify the code to use the password listing that is in a different / separate workbook with a worksheet named "Passwords"?
I tried by adding a path ahead of "Sheets" as follows (in bold) ... didn't work.
ShPassword = Application.WorksheetFunction.VLookup(ActiveSheet.Name, C:\folder name\pwsource.xlsm Sheets("Passwords") .............

I want to keep the password listing in a separate file from the main file.

Do I need quotes around the path to the external file?
Your help is much appreciated, as I am still gaining VBA knowlwdge!
Jack

[mikerickson]

Giving a password every time a sheet is activating will get old very quickly.
This Workbook_Open routine makes sheets visible, depending on which password is entered.
The user can then move from sheet to sheet without being queried on every sheet change.
[VBA]Private Sub Workbook_Open()
Dim uiPassword As String
Dim oneSheet As Worksheet
Dim SheetsToSee As String

With ThisWorkbook
.Sheets("EveryBodyCanSeeMe").Visible = xlSheetVisible
For Each oneSheet In .Sheets
If oneSheet.Name <> "EveryBodyCanSeeMe" Then
oneSheet.Visible = xlSheetVeryHidden
End If
Next oneSheet
End With

uiPassword = Application.InputBox("Enter your password", Type:=2)

Select Case LCase(uiPassword)
Case Is = "False"
Exit Sub: Rem cancel pressed
Case Is = "password1"
SheetsToSee = "Sheet1 Sheet2"
Case Is = "password2"
SheetsToSee = "Sheet1 Sheet3"
Case Is = "password3"
SheetsToSee = "Sheet2"
Case Else
MsgBox "bad password"
SheetsToSee = vbNullString
End Select

For Each oneSheet In ThisWorkbook.Sheets
If 0 < InStr(SheetsToSee, oneSheet.Name) Then
oneSheet.Visible = xlSheetVisible
End If
Next oneSheet

Rem optional hides EveryBodyCanSeeMe if password is good
ThisWorkbook.Sheets("EveryBodyCanSeeMe").Visible = IIf(SheetsToSee = vbNullString, xlSheetVisible, xlSheetHidden)

End Sub
[/VBA]

[jiddings]

Mike,
Thanks for your input/ help and I'll give that some thought.
My situation may seem unusual, however, I have mutiple worksheets in one one workbook that is accessed by numerous individuals.
I have a separate file maintaining the worksheet names (in column "A") and the corresponding individual passwords to each sheet (in column "B"). My intent was / is to loop through the listing and set the passwords for each worksheet. When I add a worksheet or change the password for another worksheet, I would like to use the listing to reset or add the password to the corresponding worksheets.
Simon and your responses are much appreciated. I do like your thoughts about making sheets visible, depending on which password is entered. I may well incorporate that into the workbook usage. Unfortunately, that doesn't appear to help with my initial issue with applying the passwords (there are mutiple passwords and worksheets) to the workbook file.
I hope this gives a better presentation of my issue, as I'm unsure that I gave the correct explanation in my initial post.
Jack

[mikerickson]

Is the situation that everyone is permitted to see every sheet, but only the authorized person can change their sheet?

OR

Is the authorized person the only one who should see that sheet?

[jiddings]

Good point! I should have explained.
The situation is that everyone is permitted to see every sheet, but only the authorized person can change their sheet.