I have the following to ask the user to log in to show their specific
worksheet in a workbook and hide the rest. This is activated when they click
the OK button in a form that pops up when Excel opens:

bOK2Use = False
bError = True
If Len(txtUser.Text) 0 And Len(txtPass.Text) 0 Then
bError = False
Select Case txtUser.Text
Case "userMe"
sSName = "Me"
If txtPass.Text < "nascar05" Then bError = True
Case "weng"
sSName = "Mine"
If txtPass.Text < "nascar07" Then bError = True
Case Else
bError = True
End Select
End If
If bError Then
MsgBox "Invalid User Name or Password"
Else
'Set document property
bSetIt = False
For Each p In ActiveWorkbook.CustomDocumentProperties
If p.Name = "auth" Then
p.Value = sSName
bSetIt = True
Exit For
End If
Next p
If Not bSetIt Then
ActiveWorkbook.CustomDocumentProperties.Add _
Name:="auth", LinkToContent:=False, _
Type:=msoPropertyTypeString, Value:=sSName
End If

Sheets(sSName).Visible = True
Sheets(sSName).Unprotect (txtPass.Text)
Sheets(sSName).Activate

bOK2Use = True
Unload UserForm1
End If

My question is, how can I modify this so that a manager can log in and see
ALL worksheets, i.e., unhide all worksheets, without getting the "you're not
authorized to view" message? Any help is greatly appreciated. Thank you!!

Just stick in a Case statement for the manager user and password. If
conditions are met, then just loop through the sheets and set them to
visible=true
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
Bowtie63 wrote:
I have the following to ask the user to log in to show their specific
worksheet in a workbook and hide the rest. This is activated when they click
the OK button in a form that pops up when Excel opens:

bOK2Use = False
bError = True
If Len(txtUser.Text) 0 And Len(txtPass.Text) 0 Then
bError = False
Select Case txtUser.Text
Case "userMe"
sSName = "Me"
If txtPass.Text < "nascar05" Then bError = True
Case "weng"
sSName = "Mine"
If txtPass.Text < "nascar07" Then bError = True
Case Else
bError = True
End Select
End If
If bError Then
MsgBox "Invalid User Name or Password"
Else
'Set document property
bSetIt = False
For Each p In ActiveWorkbook.CustomDocumentProperties
If p.Name = "auth" Then
p.Value = sSName
bSetIt = True
Exit For
End If
Next p
If Not bSetIt Then
ActiveWorkbook.CustomDocumentProperties.Add _
Name:="auth", LinkToContent:=False, _
Type:=msoPropertyTypeString, Value:=sSName
End If

Sheets(sSName).Visible = True
Sheets(sSName).Unprotect (txtPass.Text)
Sheets(sSName).Activate

bOK2Use = True
Unload UserForm1
End If

My question is, how can I modify this so that a manager can log in and see
ALL worksheets, i.e., unhide all worksheets, without getting the "you're not
authorized to view" message? Any help is greatly appreciated. Thank you!!

Hi JW, thank you! I'm still learning VB, but I tried it and it works, but it
still wants the login and passwords for the other sheets before they can
appear or they'll close. Could you tell me where to put the case statement?
I have it up on top just before the main body. Thank you for all your help!

"JW" wrote:

Just stick in a Case statement for the manager user and password. If
conditions are met, then just loop through the sheets and set them to
visible=true
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
Bowtie63 wrote:
I have the following to ask the user to log in to show their specific
worksheet in a workbook and hide the rest. This is activated when they click
the OK button in a form that pops up when Excel opens:

bOK2Use = False
bError = True
If Len(txtUser.Text) 0 And Len(txtPass.Text) 0 Then
bError = False
Select Case txtUser.Text
Case "userMe"
sSName = "Me"
If txtPass.Text < "nascar05" Then bError = True
Case "weng"
sSName = "Mine"
If txtPass.Text < "nascar07" Then bError = True
Case Else
bError = True
End Select
End If
If bError Then
MsgBox "Invalid User Name or Password"
Else
'Set document property
bSetIt = False
For Each p In ActiveWorkbook.CustomDocumentProperties
If p.Name = "auth" Then
p.Value = sSName
bSetIt = True
Exit For
End If
Next p
If Not bSetIt Then
ActiveWorkbook.CustomDocumentProperties.Add _
Name:="auth", LinkToContent:=False, _
Type:=msoPropertyTypeString, Value:=sSName
End If

Sheets(sSName).Visible = True
Sheets(sSName).Unprotect (txtPass.Text)
Sheets(sSName).Activate

bOK2Use = True
Unload UserForm1
End If

My question is, how can I modify this so that a manager can log in and see
ALL worksheets, i.e., unhide all worksheets, without getting the "you're not
authorized to view" message? Any help is greatly appreciated. Thank you!!