So, modifying the code in the link both you and lenze provided, what about something like this. In the Workbook, apart from the 'Summary' sheet and the individual sheets (eg 'Smith, John') you also have a 'LogIn' sheet like this that identifies those users with their own 'sheetname' and who is a manager and who is not.
| Excel Workbook | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | Username | Sheet Name | Manager | Password | ||
| 2 | JimJ | Jones, Jim | Y | abc | ||
| 3 | JohnS | Smith, John | Y | def | ||
| 4 | JenJ | Jones, Jen | N | |||
| 5 | KenK | Kirk, Ken | Y | kkk | ||
| 6 | BobB | Burns, Bob | N | |||
| 7 | ||||||
LogIn | ||||||
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> "Summary" Then ws.Visible = xlSheetVeryHidden
Next ws
ThisWorkbook.Save
End Sub
Private Sub Workbook_Open()
Dim user As String, pwd As String, Correctpwd As String, ShtName As String
Dim ct As Long, LR As Long
Dim C As Range
Dim ws As Worksheet
Dim Manager As Boolean
LR = Sheets("LogIn").Cells(Rows.Count, "A").End(xlUp).Row
user = InputBox("Enter your UserName")
Set C = Worksheets("LogIn").Range("$A1:$A" & LR).Find(What:=user, LookIn:=xlValues, MatchCase:=False, SearchFormat:=False)
If Not C Is Nothing Then
Manager = (C.Offset(, 2) = "Y")
If Manager Then
Correctpwd = C.Offset(, 3)
Do While ct < 3 And pwd <> Correctpwd
pwd = InputBox("Enter Password: " & 3 - ct & " tries left")
ct = ct + 1
Loop
If pwd = Correctpwd Then
For Each ws In Worksheets
If ws.Name <> "LogIn" Then
ws.Visible = True
End If
Next ws
Else
MsgBox "Incorrect manager password, access to Summary only"
End If
Else
Sheets(C.Offset(, 1).Value).Visible = True
End If
Else
MsgBox "Not a valid user, access to Summary only"
End If
End Sub| Excel Workbook | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | Username | Sheet Name | Status | Password | ||
| 2 | Admin | Admin | admin | |||
| 3 | JimJ | Manager | abc | |||
| 4 | JohnS | Manager | def | |||
| 5 | JenJ | Jones, Jen | User | jen | ||
| 6 | KenK | Kirk, Ken | User | kkk | ||
| 7 | BobB | Burns, Bob | User | bob | ||
LogIn | ||||||
Private Sub Workbook_Open()
Dim user As String, pwd As String, Correctpwd As String, ShtName As String
Dim ct As Long, LR As Long
Dim C As Range
Dim ws As Worksheet
Dim Status As String
LR = Sheets("LogIn").Cells(Rows.Count, "A").End(xlUp).Row
user = InputBox("Enter your UserName")
Set C = Worksheets("LogIn").Range("$A1:$A" & LR).Find(What:=user, _
LookIn:=xlValues, MatchCase:=False, SearchFormat:=False)
If Not C Is Nothing Then
Status = C.Offset(, 2)
Correctpwd = C.Offset(, 3)
Do While ct < 3 And pwd <> Correctpwd
pwd = InputBox("Enter Password: " & 3 - ct & " tries left")
ct = ct + 1
Loop
If pwd = Correctpwd Then
Application.ScreenUpdating = False
If Status = "User" Then
Sheets(C.Offset(, 1).Value).Visible = True
Else
For Each ws In Worksheets
ws.Visible = True
Next ws
If Status <> "Admin" Then
Sheets("LogIn").Visible = xlVeryHidden
End If
End If
Application.ScreenUpdating = True
Else
ThisWorkbook.Close
End If
Else
MsgBox "Not a valid user, access to Summary only"
End If
End Sub
