In addition to Iain's link, here's something that might help get you started.
It requires you to add a sheet and name it
LogIn.
Make SURE this sheet is at the beginning of your sheets. (ie. the furthest one to the left.)
Next, protect sheet2 with the password
Bob's Password
Protect sheet3 with the password
John's Password
Lastly, try this code in the Thisworkbook module.
Code:
Private Sub Workbook_Open()
Dim WSCount As Integer
Dim ws As Integer
WSCount = ActiveWorkbook.Worksheets.Count
Sheets("LogIn").Select
For ws = 2 To WSCount
Sheets(ws).Visible = False
Next ws
StartSequence:
uName = InputBox("Please enter your user name.", "Enter User Name")
pWord = InputBox("Please enter your password.", "Enter Password")
Select Case uName
Case "Bob"
If pWord = "Bob's Password" Then
With Sheets("Sheet2")
.Visible = True
.Select
.Unprotect "Bob's Password"
End With
Else: Ans = MsgBox("Incorrect Password. Care to try again?", vbYesNo, "Invalid Password")
If Ans = vbNo Then
Exit Sub
Else: GoTo StartSequence
End If
End If
Case "John"
If pWord = "John's Password" Then
With Sheets("Sheet3")
.Visible = True
.Select
.Unprotect "John's Password"
End With
Else: Ans = MsgBox("Incorrect Password. Care to try again?", vbYesNo, "Invalid Password")
If Ans = vbNo Then
Exit Sub
Else: GoTo StartSequence
End If
End If
'Continue with the rest of your usernames and their passwords
End Select
End Sub
Save the workbook and close/reopen it
The User name to get to sheet2 is Bob, password = Bob's Password
The User name to get to sheet3 is John, password = John's Password
If you need help tweaking the code to suit your own requirements, by all means post back with the changes you'd like to make.
Hope it helps,
Dan