Username/Password to unhide worksheets

[darin100K]

I have a workbook that opens with one visible worksheet, "Login" while the others remain hidden. In cell H22 I have a drop down listing usernames from range DA24:DA29. In cell H24 I am requesting users to enter a password. Can someone help me write some VBA code to assign different passwords for each username and then unhide specified worksheets depending on the username? I would like to display a msgbox saying something like, "the password is incorrect" if the password given does not match the appropriate username.

Let's suppose...

DA24 = Eric (password should be genius)
DA25 = Cameron (password should be mechanic)
DA26 = Larissa (password should be firefly)
DA27 = Lucy (password should be valentine)
DA28 = Sarah (password should be ricky)
DA29 = Bob (password should be rainbow)

If Eric logs in, all sheets should be unhidden. For everyone else, only sheets named "Table", "Change", and "Total" should be unhidden.

Thank you so much!

 

[agihcam]

Hi -
One option is to populate the corresponding password into next columns of username list ( db24:db29 ).
Right click the sheets "Login" and paste the code below;

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
'Return a message when UserName is Null
If Range("h22").Value = "" Then
MsgBox "User Name Should Not Be Empty" & vbLf & _
"Please select from the dropdown list!", vbCritical + vbOKOnly, "Required UserName"
Range("h22").Select
For Each ws In Sheets
    If ws.Name <> "Login" Then
        ws.Visible = False
    End If
Next
Exit Sub
End If
'If Pasword been typed, validate
If Range("h24").Value <> "" Then
    With Range("db24:db29")
        Set c = .Find(Range("h24").Value, , , xlWhole)
            If Not c Is Nothing Then
    'Unhide All sheets when UserName "Eric" and correct Password was type-in
                If c.Offset(, -1) = Range("h22").Value And Range("h24").Value = "genius" Then
                    For Each ws In Sheets
                        ws.Visible = True
                    Next
                Else
    'Unhide Sheets "Login", "Table","Change" & "Total" when UserName is not "Eric"
                    For Each ws In Sheets
                        ws.Visible = True
                        If ws.Name <> "Login" And ws.Name <> "Table" And ws.Name <> "Change" And ws.Name <> "Total" Then
                            ws.Visible = False
                        End If
                    Next
                End If
            Else
    'Return a message when Password is incorrect
            MsgBox "The Password is Incorrect!", vbCritical + vbOKOnly, "Incorrect Password"
            Range("h24").ClearContents
            Range("h24").Select
            For Each ws In Sheets
                If ws.Name <> "Login" Then
                    ws.Visible = False
                End If
            Next
            End If
    End With
End If
Application.ScreenUpdating = True
End Sub

 

[DominicB]

Good morning darin100K

I have a template available that will open with all sheets except for one blank one hidden. A userform prompts for a username and password and once entered correctly the sheets that the particular person is allowed to see are unhidden (using the xlVeryHidden instruction). The user rights are assigned by the administrator who can see all sheets and settings. The workbook is quite configurable and contains full instructions to get you started.

I can send this to anyone requesting it via e-mail.

HTH

DominicB
dominic@dom-and-lis.co.uk

 

[darin100K]

Thank you so much for your response. It looks like you spent a lot of energy for me. I did, however, forget to mention that I need the Macro to run from a button. How would that change what you've written?

Thank You again.

 

[agihcam]

see revised code.
i would suggest to try Dominic spread sheet also.

 

[darin100K]

I'm having better luck with your revised solution. Thanks for that. However, I'm not getting any msgboxes and any of the passwords will work with any of the names.

For instance, Cameron can type his password, "mechanic" with username "Eric" and gain access to all the sheets. I'd like it where the password "mechanic" is only given to Cameron and will only work with username Cameron.

What do you think?

 

[acw]

Hi

A slightly different approach. The password list is in the macro and not on the login sheet.

You don't say what type of button you want to use. If it is a control toolbox button, then put the code directly into the event code area. If a forms button, then assign it to the macro.

Sub ddd()
  For Each ws In Sheets
    If ws.Name <> "Login" Then
        ws.Visible = False
    End If
  Next ws
  
  If IsEmpty(Range("H22")) Then
      MsgBox "Please select a user name"
      Exit Sub
  End If
  If IsEmpty(Range("H24")) Then
    MsgBox "Please enter password"
    Exit Sub
  End If
  pwarray = Array("genius", "mechanic", "firefly", "valentine", "ricky", "rainbow")
  If Range("H24") <> pwarray(WorksheetFunction.Match(Range("H22"), Range("da24:da29"), 0) - 1) Then
    MsgBox "Incorrect Password"
    Exit Sub
  End If
  
  If Range("H22") = "Eric" Then
    For Each ws In Worksheets
      ws.Visible = True
    Next ws
  Else
    
    Sheets("Table").Visible = True
    Sheets("Change").Visible = True
    Sheets("Total").Visible = True
    Sheets("Login").Visible = False
  End If

End Sub

Not sure if you want the login sheet to be visible to other users so I've turned it off for all but Eric.


Tony