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

 

[Akuini]

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>

Hi, bebe1279
Try this:

Private Sub CommandButton1_Click()
Dim user As String
Dim pass As String
Dim r As Range
Dim adminName As Range
Dim regUser As Range
Dim Ws As Worksheet
Dim shA As Worksheet
Dim d As Object
Dim flag As Boolean
Dim va, q

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

Set shA = Sheets("admin control")

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

    For Each Ws In ActiveWorkbook.Worksheets
        Ws.Visible = True
    Next Ws

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

For Each r In regUser

    If user = r.Value And pass = r.Offset(0, 1).Value Then
      va = shA.Range(r.Offset(0, 2), r.Offset(0, 7))
        
        For Each Ws In ActiveWorkbook.Worksheets
            flag = False
            For Each q In va
                If LCase(Ws.Name) = LCase(q) Then flag = True
            Next
            If flag = False Then Ws.Visible = False

        Next Ws
        Unload Me
        Exit Sub
    End If
Next

End Sub

But I don't know how you set up the login form.
What happen if a user exiting the form by clicking the exit button at the up right corner of the form?

 

[bebe1279]

Hi Akuini
Thank you for your reply. I tried your code and the first part, the one that checks the admin group logins, works perfectly, thanks. however, the users with restrictions doesn't work. No error message appears though. The code runs and finishes but nothing happens. The form doesn't unload and all sheets are visible.
As far exiting the form by Xing out, I have not yet put anything into place, partly because I'm not sure how to go about it, haven't looked it up yet and partly because I wanted to try and get this part working the way I'm hoping it will. Any help on that matter would greatly be appreciated as well.

 

[Akuini]

I use this sample to try the macro. And it works.
The login data should be in sheets 'admin control'.
Try it and if it still doesn't work, can you post some sample.

Book1
	A	B	C	D	E	F	G	H	I
1
2			a	1		c	3	sheet1	sheet3
3			b	2		d	4	sheet2
4						e	5	sheet3
admin control

 

[dmt32]

Hi,
have a look at this article & see if could be any help in your project:Excel Userform Login - Multiple Level - Plus +++ - Online PC Learning

Dave

 

[bebe1279]

Okay, so I'm going to describe the way "admin controls' is setup.
All the tables have headers.
A1:A21 hold the sheet name list.
B column is empty
C1:C6 hold the admin usernames, D1:D6 holds the admin passwords
E column is empty
F1:F17 holds the usernames for the restricted user, G1:G17 holds the password for these users
Columns H through M holds the names of the allowable sheets for each user.
I know a pic would have been better, but I hope this helps you visualize the way my data is set up

 

[bebe1279]

Thanks Dnt32
I'll check out the vid later. I can't keep my eyes open anymore.

 

[Akuini]

Okay, so I'm going to describe the way "admin controls' is setup.
All the tables have headers.
A1:A21 hold the sheet name list.
B column is empty
C1:C6 hold the admin usernames, D1:D6 holds the admin passwords
E column is empty
F1:F17 holds the usernames for the restricted user, G1:G17 holds the password for these users
Columns H through M holds the names of the allowable sheets for each user.
I know a pic would have been better, but I hope this helps you visualize the way my data is set up

Actually my data sample is set up like that (well I left col A blank but it doesn't matter).
a,b,c .. is user
1,2,3,.. is password
Can you post your sample in table just like I posted?

 

[Akuini]

I upload my workbook sample to dropbox:
https://www.dropbox.com/s/rzgi12icsmwgsxq/a1007041-help-multi-level-access-login-form.xlsm?dl=0

 

[dmt32]

Okay, so I'm going to describe the way "admin controls' is setup.
All the tables have headers.
A1:A21 hold the sheet name list.
B column is empty
C1:C6 hold the admin usernames, D1:D6 holds the admin passwords
E column is empty
F1:F17 holds the usernames for the restricted user, G1:G17 holds the password for these users
Columns H through M holds the names of the allowable sheets for each user.

Hi,
Not fully tested but see if this update to your code does what you want.

rivate 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
    
'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(m, 1).Offset(, 1).Value = pass Then
            Sheet3.Visible = xlSheetVisible
            Unload Me
        Else
'password not valid
            MsgBox "Invalid Password", 16, "Invalid"
        End If
    Else
'check if reg user
        m = Application.Match(user, RegUser, False)
'validate password
        If RegUser.Cells(m, 1).Offset(, 1).Value = pass Then
        On Error GoTo myerror
'unhide user sheets
            c = 2
            Do
                Worksheets(RegUser.Cells(m, 1).Offset(, c).Value).Visible = xlSheetVisible
                c = c + 1
            Loop Until RegUser.Cells(m, 1).Offset(, c).Value = ""
            Unload Me
        Else
'password not valid
            MsgBox "Invalid Password", 16, "Invalid"
        End If
    End If
myerror:
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Dave