Hide /Unhide A Worksheet based on a password.

[DannyJurmann]

I have a spreadsheet which tracks staff sickness and leave for 100 people. Each staff member has their own worksheet and there is a summary sheet showing sickness by staff member in a graph. I am trying to set it up so that each staff member can only see their own sheet and only the manager can see the staff sickness graph.

Looking at previous posts I cannot see a good solution. The standard answer seems to be Hiding the sheets and protecting the workbook structure but I cannot see how this allows for different passwords on different sheets (unhiding them is also not intuitive and my colleagues are not good with IT - I ended up going into the VBA screen).

I did wonder about having a cover sheet with 2 cells - one with a drop down with staff names (Usernames) and a second cell for the password and then have the worksheet open if the values are correct (so unhide a spreadsheet based on cell value) but am a bit vague as to how to go about it.

Any thoughts would be most appreciated!

Best wishes,

Dannny

 

[Peter_SSs]

Welcome to the MrExcel board!

I addressed a couple of like requests in this thread. See if it suits you or if you can adapt, otherwise post back with details of your difficulties.

 

[Scott T]

The standard answer seems to be Hiding the sheets and protecting the workbook structure but I cannot see how this allows for different passwords on different sheets.

Something like this will unhide a sheet or sheets depending on the password entered into the input box. You could also use a password entered into a cell and if wanted a username.

mypass = Application.InputBox( _
    prompt:="enter password")
    
If mypass = "rickspassword" Then Sheets("Rick").Visible = True
If mypass = "Bethspassword" Then Sheets("Beth").Visible = True

 

[DannyJurmann]

Welcome to the MrExcel board!

I addressed a couple of like requests in this thread. See if it suits you or if you can adapt, otherwise post back with details of your difficulties.

Hi Pete,

Thank you very much!

I have tried the code you posted and the code posted by Lenze but cannot get either to work...but then the macros do not appear in the list either. In both cases when I save and open the spreadsheet everything opens without prompting for a username or password. The file is saved as .xlsm and my protect / unprotect macros are visible and working fine. All VBA modules are set to "General"

for Lenze's solution I have created a login sheet

Column A - Username
Column B - Password
Column C - Sheet Name

Theory: If they do not enter the correct username /password then only the Instructions sheet will be visible

Lenze's code:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Sheets
If ws.Name <> "Instructions" Then ws.Visible = xlSheetVeryHidden
Next ws
End Sub


Private Sub Workbook_Open()
Dim user As String
Dim pwd As String
Dim ct As Integer
Dim C As Range
Dim LR As Long
LR = Sheets("LogIn").Cells(Rows.Count, "A").End(xlUp).Row
user = InputBox("Enter your UserName")
Set C = Worksheets("LogIn").Range("$A1:$A" & LR).Find(user, LookIn:=xlValues)
    If C Is Nothing Then
        MsgBox "Unauthorized to proceed"
        Me.Close
    End If
ct = 10
retry:
    pwd = InputBox("Enter Password")
    If pwd <> Sheets("LogIn").Cells(C.Row, 2) Then
    If ct = 0 Then
       MsgBox "Out of tries"
       Me.Close
   End If
       MsgBox "Wrong Password." & Chr(10) & "You have " & ct & " tries left"
       ct = ct - 1
       GoTo retry
   End If
Dim ws As Worksheet
For Each ws In Sheets
If ws.Name = Sheets("LogIn").Cells(C.Row, "C") Then ws.Visible = xlSheetVisible
Next ws
End Sub

For your solution I created a sheet LoginX as shown in your instructions

Column A - Username
Column B - Sheetname
Column C - Manager
Column D - Password

Pete's code

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Sheets
If ws.Name <> "Instructions" Then ws.Visible = xlSheetVeryHidden
Next ws
End Sub


Private Sub Workbook_Open()
Dim user As String
Dim pwd As String
Dim ct As Integer
Dim C As Range
Dim LR As Long
LR = Sheets("LogInX").Cells(Rows.Count, "A").End(xlUp).Row
user = InputBox("Enter your UserName")
Set C = Worksheets("LogInX").Range("$A1:$A" & LR).Find(user, LookIn:=xlValues)
    If C Is Nothing Then
        MsgBox "Unauthorized to proceed"
        Me.Close
    End If
ct = 10
retry:
    pwd = InputBox("Enter Password")
    If pwd <> Sheets("LogInX").Cells(C.Row, 2) Then
    If ct = 0 Then
       MsgBox "Out of tries"
       Me.Close
   End If
       MsgBox "Wrong Password." & Chr(10) & "You have " & ct & " tries left"
       ct = ct - 1
       GoTo retry
   End If
Dim ws As Worksheet
For Each ws In Sheets
If ws.Name = Sheets("LogInX").Cells(C.Row, "C") Then ws.Visible = xlSheetVisible
Next ws
End Sub

Bit stymied For both Lenze's code and your own nothing changes.

Thank you for your help,

Danny

 

[DannyJurmann]

Thank you Scott

 

[Peter_SSs]

Hi Pete,

I have tried the code you posted ...

Which code (ie which post) as I posted several codes in that thread?

 

[DannyJurmann]

Hi Pete!


You are brilliant! I realised my first problem was I put the code into a module not "this workbook" and now it works a lot better! (Thank you). However there is one issue which is stopping it working (doubtless I am doing something wrong). I am finding it only asks for a password for users where the manager is defined as Y. Users who are not managers can simply add their usernames and they are in. The reason this is a problem is that if I wanted to see when my colleague was on holiday (for my own nefarious reasons) I could simply type in their username and see everything.

Not stopping anything working but would be nice is...I was also wondering if it is possible to have multiple opportunities to retype the username?

Thank you ever so much,

Danny

 

[DannyJurmann]

Hi Pete,

Sorry I was not clear - this code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ws As Worksheet
    
    For Each ws In Worksheets
    If ws.Name <> "Instructions" 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 INSTRUCTIONS only"
                End If
            Else
                Sheets(C.Offset(, 1).Value).Visible = True
            End If
        Else
            MsgBox "Not a valid user, access to Instructions only"
        End If
End Sub

 

[Peter_SSs]

When posting code, please use Code Tags to preserve the indentation formatting as it makes the code much easier to read, debug & work with if copied to our own workbook. My signature block below explains how. I have added the Code Tags to your earlier posts.

I don't think you read far enough through that other thread. It looks like you are trying to use code adapted from post 7 in it. At that point the code only required a password if it was a Manager trying to sign in (note that the non-Managers had no password recorded in the 'LogIn' sheet).

In post 8 the OP then asked that all users must enter a username and password. Post 9 addressed that issue so I suggest you see if you can get that code adapted. If not, post your attempted code and explain in what way it fails & I'll see if I can help.

Once we get that working, I'll also look at the possibility of allowing multiple entries of username.