Excel VBA & UserForm Login and Password VLOOKUP Table in Sheet

Question

I've been trying to get my login userform to login when clicked based on data in a table in the workbook, but I just can't seem to get the code right.

Details are:

Userform username textbox = UsernameTextbox;

Userform password textbox = PasswordTextbox;

Userform submit button = LoginButton

My workbook has a number of sheets, one of which is "Users". In that sheet, there is a table called "Users_Table". That table has 4 columns:

ID (individual IDs for users) [Column A],

Username [Column B],

Password [Column C],

Admin (answer is "True" or "False" depending on if they have admin rights) [Column D].

I'm trying to do this: If the username and password is correct for a user AND if the admin column entry is False, then I want to show sheets "Quick Add" and "Overview", I want to make the sheet "Admin" hidden (not VeryHidden since I need to use data on this sheet for other macros), and make "User" sheets VeryHidden so those logged in can't see other users' details. But for users who correctly enter their username and password AND for whom the admin column entry is True, I want to show all sheets.

This is what I have so far:

Private Sub LoginButton_Click() 
  Dim Username As String 
  Username = UsernameTextbox.Text 
  Dim password As String 
  Password = PasswordTextbox.Text 
  If IsNull(Me.UsernameTextbox) Or Me.UsernameTextbox = "" Then 
    MsgBox "You must enter your username.", vbOKOnly, "Required Data" 
    Me.UsernameTextbox.SetFocus 
    Exit Sub 
  End If 
  If IsNull(Me.PasswordTextbox) Or Me.PasswordTextbox = "" Then 
    MsgBox "You must enter your Password (case sensitive).", vbOKOnly, "Incomplete Entry" 
    Me.PasswordTextbox.SetFocus 
    Exit Sub 
  End If
  Dim temp As String 
  On Error Resume Next 
  temp = WorksheetFunction.VLookup(Me.UsernameTextbox.Value, Worksheets("Users").Range("Users_Table"), 2, 0)
  If Username = temp Then 
    Err.Clear 
    temp = "" 
    temp = WorksheetFunction.VLookup(Me.UsernameTextbox.Value, Worksheets("Users").Range("Users_Table"), 3, 0) 
    On Error Goto 0 
    If Password = temp Then 
      Sheets("Quick Add").Visible = xlSheetVisible 
      Sheets("Overview").Visible = xlSheetVisible 
      Sheets("Admin").Visible = xlSheetHidden 'This is now just Hidden and not VeryHidden since other macros need to use data on this sheet
      Sheets("Users").Visible = xlVeryHidden 
      MsgBox "Password and Username Accepted. You are now Logged In." 
      'Unload Me
      'Sheets("Quick Add").Select
      'Range("A1").Select
    Else 
      Sheets("Quick Add").Visible = xlVeryHidden 
      Sheets("Overview").Visible = xlVeryHidden 
      Sheets("Admin").Visible = xlVeryHidden 
      Sheets("Users").Visible = xlVeryHidden 
      MsgBox "Username and Password Combination Not Accepted"
    End If 
  Else 
    Sheets("Quick Add").Visible = xlVeryHidden 
    Sheets("Overview").Visible = xlVeryHidden 
    Sheets("Admin").Visible = xlVeryHidden 
    Sheets("Users").Visible = xlVeryHidden 
    MsgBox "Invalid Username"
  End If
End Sub

This works for the first entry in the "Users_Table", but it won't recognise the Username for the others (and so I don't know if it's recognising the Passwords for users as it's failing on the initial Username check). Any ideas what might be going wrong? I'm also not sure how I'd go about adding in the Admin requirement mentioned above. I need Admins ("True" in "Admin" column, i.e. Column D, in the "Users_Table") to be able to see all sheets; the code above is just for Users and shows "Quick Add" and "Overview" and hides "Admin" and "Users" sheets.

Any help would be much appreciated. Thank you!

A.S.H · Accepted Answer · 2017-06-03 20:03:32Z

1

Any ideas what might be going wrong?

There are a few errors in the code that don't match your description.

temp = WorksheetFunction.VLookup(Me.UsernameTextbox.Value, _
  Worksheets("Users").Range("Users_Table"), 2, 0)
If Username = temp Then

Here you are matching the UsernameTextbox to column A (ID). The test for existence of the username should be in column B not A. The same mistake is made where you are matching the username onto the ID column A insread of the column B of user names:

temp = WorksheetFunction.VLookup(Me.UsernameTextbox.Value, _
   Worksheets("Users").Range("Users_Table"), 3, 0)

The best approach would be to fetch to row of the user at once (if it exists) and from there get all the attributes.

Private Sub LoginButton_Click()
  ' Get the user row or exit if not found
  Dim r As Range
  Set r = Worksheets("Users").Range("Users_Table").Columns(2) _
    .Find(UsernameTextbox.text, , xlValues, xlWhole)
  If r Is Nothing Then
    MsgBox "username not found."
    Me.UsernameTextbox.SetFocus
    Exit Sub
  End If
  If Me.PasswordTextbox.Value <> r.Offset(, 1).Value2 Then
    MsgBox "Wrong Password."
    Me.PasswordTextbox.SetFocus
    Exit Sub
  End If

  ' So far user and password are ok
  Dim isAdmin As Boolean: isAdmin = r.Offset(, 2).Value2
  Sheets("Quick Add").Visible = xlSheetVisible
  Sheets("Overview").Visible = xlSheetVisible
  Sheets("Admin").Visible = IIf(isAdmin, xlSheetVisible, xlSheetHidden)
  Sheets("Users").Visible = IIf(isAdmin, xlSheetVisible, xlSheetVeryHidden)
End Sub

edited Jun 3, 2017 at 20:03

answered Jun 3, 2017 at 18:14

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

akrasia Over a year ago

Thank you so much for taking the time to respond. Your answer worked great, but I've gone with Siddharth Rout's answer as their code was a bit more streamlined. I'll keep playing with both to see which works best. Many thanks again.

Siddharth Rout · Accepted Answer · 2017-06-03 18:21:04Z

You have made it very complicated. Keep it simple. Try this (untested)

Private Sub LoginButton_Click()
    Dim Username As String
    Dim password As String
    Dim passWs As Worksheet
    Dim rng As Range
    Dim CorrectDetails As Boolean

    Username = UsernameTextbox.Text
    password = PasswordTextbox.Text

    If Len(Trim(Username)) = 0 Then
        UsernameTextbox.SetFocus
        MsgBox "Please enter the username", vbOKOnly, "Required Data"
        Exit Sub
    End If

    If Len(Trim(password)) = 0 Then
        PasswordTextbox.SetFocus
        MsgBox "Please enter the password", vbOKOnly, "Incomplete Entry"
        Exit Sub
    End If

    Set passWs = ThisWorkbook.Worksheets("Users")

    With passWs
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 1 To lrow
            If UCase(Trim(.Range("B" & i).Value)) = UCase(Trim(Username)) Then '<~~ Username Check
                If .Range("C" & i).Value = password Then '<~~ Password Check
                    CorrectDetails = True

                    '~~> Admin is True
                    If .Range("D" & i).Value = "True" Then
                        '
                        '~~> Do what you want
                        '
                    Else
                        '
                        '~~> Do what you want
                        '
                    End If

                    Exit For
                End If
            End If
        Next i

        '~~> Incorrect Username/Password
        If CorrectDetails = False Then
            MsgBox "Invalid Username/Password"
        End If
    End With
End Sub

My Assumptions

In sheet "Users", Col B has username, Col C has password and Col D has Admin values.. If not then please amend the above code as required.

Sergii Golovynskyi · Accepted Answer · 2021-09-29 10:11:42Z

Hello everyone,
I know that is was a long time ago, but maybe it would be useful for sm1 the code above did not work for me, so I modify it for my requirements.

Some details of my code: CommandButton2 it is my "LogIn Button";
TextBox5 it is my "User / Admin name";
TextBox7 it is my "User / Admin password";
Worksheets("LOG") it is a name and location of the table with "User / Admin names and passwords" data, where col B - usernames, col C - user passwords, col d - admin names, col e - admin passwords. The difference between admin and user rights in my case only in visibility of application (Excel).

Private Sub CommandButton2_Click()

Dim passWs As Worksheet
Dim CorrectDetails As Boolean

Username = TextBox5.Text
password = TextBox7.Text

If Len(Trim(Username)) = 0 Then
    TextBox5.SetFocus
    MsgBox "Please enter the username", vbOKOnly, "Required Data"
    Exit Sub
End If

If Len(Trim(password)) = 0 Then
    TextBox7.SetFocus
    MsgBox "Please enter the password", vbOKOnly, "Incomplete Entry"
    Exit Sub
End If

Set passWs = ThisWorkbook.Worksheets("LOG")

With passWs
    lRow = .Range("B" & .Rows.Count).End(xlUp).Row
    
    For i = 1 To lRow
        If UCase(Trim(.Range("B" & i).value)) = UCase(Trim(Username)) Then '<~~ Username Check
            If .Range("C" & i).value = password Then '<~~ Password Check
                CorrectDetails = True
                
                If CorrectDetails = True Then
                    Application.Visible = False
                    Me.TextBox5.Text = ""
                    Me.TextBox7.Text = ""
                    LogIn.Hide
                    UserForm1.Show
                End If
                
                Exit For
            End If
        End If
        
        If UCase(Trim(.Range("D" & i).value)) = UCase(Trim(Username)) Then '<~~ Adminname Check
            If .Range("E" & i).value = password Then '<~~ Admin Password Check
                CorrectDetails = True
                
                If CorrectDetails = True Then
                    Application.Visible = True
                    Me.TextBox5.Text = ""
                    Me.TextBox7.Text = ""
                    LogIn.Hide
                End If
                
                Exit For
            End If
        End If
    Next i

    '~~> Incorrect Username/Password
    If CorrectDetails = False Then
        MsgBox "Invalid Username/Password"
    End If
End With

End Sub

Collectives™ on Stack Overflow

Excel VBA & UserForm Login and Password VLOOKUP Table in Sheet

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related