Showing specific sheets by entering username and password

[Polxr]

I want to make a workbook where multiple users have their own username and password, and keying in a specific username and password will show specific sheets.
I tried writing the code below for the userform but it does not work. I have attached an example file, and i want to hide the rest of the sheets except the home page.
So for example, when user 1 key in their usename and password, only the products sheet will show, whereas for user 2, only quantity sheet will show.

Private Sub btnOK_Click()

    Dim user As Variant, pass As Variant
    Dim Error As Boolean
    Dim ws As Worksheet
    user = Me.txtUser.Value
    pass = Me.txtPass.Value
    
    If user = "user1" And pass = "1234" Then
    Set ws = Worksheets("Sheet2")
    ws.Visible = True
    Else: Error = True
    End If
    
    If Error Then
        MsgBox "Invalid User Name or Password"
    End If
    
    Unload Passwordform
    Exit Sub
End Sub

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)


Administrative Note:

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Mumps1]

If there are multiple users each with a different password and each with access to different sheets, you would need a list, perhaps in its own sheet, with that information. Can you attach a copy of your file (de-sensitized if necessary) which includes the information I mentioned?

[trunten]

Hi There,

I've just quickly thrown together a rough example. If you take a quick peek at the code you will see it's pretty straight forward.

Current users are user1, user2, user3 and user4 and have the passwords, changeme1, changeme2, changeme3 and changeme4 respectively.
user1 has "Admin" rights so if you log in as them you will see all the sheets in the workbook.

hth.

Mod edit: code added below for the benefit of all.

Function checkLogin(ByVal username As String, ByVal password As String) As Boolean
    With Sheets("Usernames")
        If Application.CountIf(.Range("A:A"), username) > 0 Then
            If Application.VLookup(username, .Range("A:B"), 2, False) = password Then
                checkLogin = True
            Else
                checkLogin = False
            End If
        Else
            checkLogin = False
        End If
    End With
End Function

Function userlevel(ByVal username As String) As String
    With Sheets("Usernames")
        If Application.CountIf(.Range("A:A"), username) > 0 Then
            userlevel = Application.VLookup(username, .Range("A:C"), 3, False)
        Else
            userlevel = ""
        End If
    End With
End Function

Sub showSheets(ByVal userlevel As String)
    Dim sht As Worksheet
    Dim c As Range
    With Sheets("Access_Rights").Range("A2", Sheets("Access_Rights").Cells(Sheets("Access_Rights").Rows.Count, 1).End(xlUp))
        If .Cells(1, 1).Row > 1 Then
            For Each c In .Cells
                Set sht = Nothing
                If StrComp(c.Value, userlevel, vbTextCompare) = 0 Then
                    On Error Resume Next
                    Set sht = Sheets(c(1, 2).Value)
                    On Error GoTo 0
                    If Not sht Is Nothing Then sht.Visible = True
                End If
            Next c
        End If
    End With
End Sub

Private Sub setUser(ByVal username As String)
    Dim sht As Worksheet
    
    If username = "" Then Exit Sub
    Application.Names("currentUser").Value = username
    If Application.CountIf(Sheets("Usernames").Range("A:A"), username) > 0 Then
        On Error Resume Next
        Set sht = Sheets(Application.VLookup(username, Sheets("Usernames").Range("A:D"), 4, False))
        On Error GoTo 0
        If Not sht Is Nothing Then sht.Select
    End If
End Sub

Private Sub hideWelcomePage()
    Dim sht As Worksheet
    
    For Each sht In Sheets
         If Not sht Is Sheets("Welcome") Then
            If sht.Visible = xlSheetVisible Then
                Sheets("Welcome").Visible = False
                Exit For
            End If
         End If
    Next sht
End Sub

Sub logout()
    Dim sht As Worksheet
    
    Sheets("Welcome").Visible = True
    For Each sht In Sheets
        If Not sht Is Sheets("Welcome") Then sht.Visible = xlSheetVeryHidden
    Next sht
End Sub

Sub login()
    Dim username As String
    Dim password As String
    
    username = Application.InputBox("Username", Type:=2)
    password = Application.InputBox("Password", Type:=2)
    If checkLogin(username, password) Then
        showSheets userlevel(username)
        setUser username
        hideWelcomePage
    Else
        MsgBox "Incorrect login details", vbCritical
    End If
End Sub

[jason.b75]

If this is on a network where each user has their own windows user id, then you could use

Application.Username

to show the sheets that the given user is permitted to see.