Help with multi level access login form

[bebe1279]

Hi everyone<o></o>
I’m trying to create a login form that will grant access to the workbook and its sheets based on data stored in a sheet named ‘admin control’. <o></o>
The admin control sheet has three tables. One table (range A2:A21) that lists the sheet names (populated by a macro), another table (C2:D6) that holds the user name (C) and password (D) of the persons who will have full access to the entire workbook and the third table (F2:M17) holds the user name (F) and password (G) as well as the sheet names (H:M) of the sheets I want that person to be able to access. <o></o>
What I want to be able to do is take the login info entered by the user and compare it to the data in the admin group, if there is a match then close the form and open the workbook to the admin control sheet, all worksheets are visible. If there isn’t a match in that table, then check to see if there is a match in the next table. If a match is found, then set only the sheets listed to the right (H-M) as visible.


Any help is much appreciated

I've been trying to work out the first part; validating user info against admin group. This is what I have

Private Sub CommandButton1_Click()

'declaring variables
Dim user As String
Dim pass As Variant

'assigning value to variables
user = Usertxtbox.Value
pass = Passtxtbox.Value
Login = user + pass

Set adminName = Sheet3.Range("C2:C6")
Set regUser = Sheet3.Range("F2:F17")

'validating username & passcode of admins & set access
For Each Login In adminName
    If user = adminName.Value And pass = adminName.Offset(0, 1).Value Then
        Unload Me
        Sheet3.Select
    End If
Next Login

end sub

but it gives me a type mismatch error on this line

f user = adminName.Value And pass = adminName.Offset(0, 1).Value Then

 

[bebe1279]

Hi Akuini
Here is a link to dropbox with a sample "admin controls" sheet.
https://www.dropbox.com/s/u1i1r08xzoig98y/Project screen shot.PNG?dl=0

I've been working on the code and have gotten most of it to work except for showing only the sheets listed by each user, the red table in the link above.

 

[Akuini]

Hi Akuini
Here is a link to dropbox with a sample "admin controls" sheet.
https://www.dropbox.com/s/u1i1r08xzoig98y/Project screen shot.PNG?dl=0

I've been working on the code and have gotten most of it to work except for showing only the sheets listed by each user, the red table in the link above.

1. Can you upload a sample workbook (not just a picture)?
2. Did you try my sample workbook?

 

[bebe1279]

I appreciate the help you guys are giving me and sorry it took so long to reply.

dmt32
I tried your code, it runs without errors but both admin and regUser opens all the worksheets.

Akuini
here is a link to a sample "admin controls" sheet
https://www.dropbox.com/s/u1i1r08xzoig98y/Project screen shot.PNG?dl=0

 

[dmt32]

I appreciate the help you guys are giving me and sorry it took so long to reply.

dmt32
I tried your code, it runs without errors but both admin and regUser opens all the worksheets.

Hi,
you appear to being running a thread with very similar issue here:https://www.mrexcel.com/forum/excel-questions/1007283-object-required-error.html#post4835011
a MOD may have something to say if it is spotted.

To cover your point about my solution - As you only shared the login part of your code, I assumed code to hide sheets existed elsewhere in your project & that they were already hidden at time the form is displayed.

If this is not the case then and update to my solution & some additional code would be required.

Updated Code:

Private Sub CommandButton1_Click()
'declaring variables
    Dim user As String, pass As String
    Dim m As Variant
    Dim c As Integer
    Dim AdminName As Range, RegUser As Range
    Dim sh As Object
    
'assigning value to variables
    user = Usertxtbox.Value
    pass = Passtxtbox.Value
    
'ensure user data entered
    If Len(user) = 0 Or Len(pass) = 0 Then Exit Sub
'set admin range
    Set AdminName = Sheet3.Range("C2:C6")
'set user range
    Set RegUser = Sheet3.Range("F2:F17")
'check if user is Admin
    m = Application.Match(user, AdminName, False)
    If Not IsError(m) Then
'validate Admin password
        If AdminName.Cells(CLng(m), 1).Offset(, 1).Value = pass Then
'unhide all sheets
            For Each sh In ThisWorkbook.Sheets
                sh.Visible = xlSheetVisible
            Next sh
'admin sheet
            Sheets("Admin Controls").Select
            Unload Me
        Else
'password not valid
            MsgBox "Invalid Password.", 16, "Invalid"
        End If
    Else
'admin user not found
'check if reg user
        m = Application.Match(user, RegUser, False)
        If Not IsError(m) Then
'validate password
            If RegUser.Cells(CLng(m), 1).Offset(, 1).Value = pass Then
                On Error GoTo myerror
'unhide user sheets
                c = 2
            With RegUser.Cells(CLng(m), 1)
                Do
                   Worksheets(.Offset(, c).Value).Visible = xlSheetVisible
                    c = c + 1
                Loop Until .Offset(, c).Value = ""
             End With
                Unload Me
            Else
'password not valid
                MsgBox "Invalid Password.", 16, "Invalid"
            End If
        Else
'invalid user name
                MsgBox "Invalid User Name.", 16, "Invalid Name"
        End If
    End If


myerror:
'report errors
    If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Additional code to hide sheets:

Private Sub UserForm_Initialize()
    Dim i As Integer
    With ThisWorkbook
    For i = 2 To .Worksheets.Count
        .Worksheets(i).Visible = xlSheetVeryHidden
    Next i
    End With
End Sub

Code assumes the FIRST sheet in your workbook remains visible.

Dave