Userform as username and password login screen

[ajmoore28]

I am new to VBA programming and I am having difficulty with a userform that I have set-up. Essentially I have set-up the userform to use as a login in screen, it has a combo box for the username and a textbox for the password. I have a separate hidden worksheet that I am using to maintain my user list which has two columns; one for username and one for password. Currently I have 19 different users. I have set-up the combo box to pull in the usernames using rowsource in properties. I want the user to be able to pick their username from the drop down list then type in their password. The problem is I have no idea how to code this. I have no problem when we are only talking about one login and one password but I don't know how to set this up for mutiple sets. Any help would be very much appreciated....

 

[Chris Bode]

Suppose sheet3 contains user name and password
Add following code in login button click event

Private Sub cmdLogIN_Click()
    Dim row As Integer, col As Integer
    row = 1
    col = 1

    Dim status As Boolean
    status = False
    For row = 1 To 19
        If Sheet3.Cells(row, col).Value = cmbName.Value And Sheet3.Cells(row, col + 1).Value = txtPwd.Text Then
            status = True
            Exit For
        End If
    Next
    
    If Not status Then
        MsgBox "Log In failed", vbCritical
    Else
        Unload Me
    End If
End Sub

 

[mikerickson]

This assumes that the list of user names is in a named range "UserRange" and that the password is one cell to the right of the cell with the username.

This code is for UserForm1 which has the controls ComboBox1, TextBox1, CommandButton1, and Label1.

This goes in the userform's code module.

Private Sub CommandButton1_Click()
    If 0 <= ComboBox1.ListIndex Then
        Rem case sensitive
        Me.Tag = CStr(Range("UsersRange").Find(ComboBox1.Text).Offset(0, 1).Value = TextBox1.Text)
    End If
    Me.Hide
End Sub

Private Sub UserForm_Initialize()
    Label1.Caption = "Select a User, enter a password and press Enter."
    CommandButton1.Caption = "Enter"
    TextBox1.PasswordChar = Chr(165)
    ComboBox1.List = Range("UsersRange").Value
End Sub

Private Sub ComboBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = 0
End Sub

In a normal module, the function UserKnowsPassword returns True or False if the user entered the correct info. Demo shows how the function is used in a routine.

Function UserKnowsPassword() As Boolean
    UserForm1.Show
    UserKnowsPassword = (UserForm1.Tag = "True")
    Unload UserForm1
End Function

Sub demo()
    If UserKnowsPassword Then
        MsgBox "the password entered matches the username chosen."
    Else
        MsgBox "user did not match password"
    End If
End Sub

 

[ajmoore28]

Awesome...thank you both. It is working exactly as I wanted it to with some minor tweaking. This site is a life saver!