For Next Loop - If Then

[amartin575]

Hello,

I am looking for vba code that uses For Next Loop with If Then in between that tests a username and password login in excel and tests to see if the combination is valid. If not it fails it if so it is successful. If any combination is in error it should fail it. The code is broken up because I started using a form to create it which was working however the requirement is to use For, Next, Loop with If Then to test the login credentials against a table within the workbook. This is the code.

Sub Login2()
Set WsUserName = Sheets("Sheet2")
For i = 1 To 100
Range("A" & i).Value = i
'Set RgUserPas = WsUserName.Range("A2:A100")
Dim username As String, StartCell As Range
    username = InputBox("Enter your username:", username)

'Set c = RgUserPas.Find(TxtUserName.Value, LookIn:=xlValues, MatchCase:=False)
password = c.Offset(0, 1).Value
Level = c.Offset(0, 2).Value
If TxtUserName.Value = "" Then
MsgBox "Insert Username", vbCritical, "User Login"
Exit Sub
End If
If TxtPassword.Value = "" Then
MsgBox "Insert Password", vbCirtical, "User Login"
Exit Sub
End If
If TxtPassword <> password Then
MsgBox "User Name or Password is Wrong", vbCritical, "User Login"
Exit Sub
Else
MsgBox "Login Success", vbInformation, "User Login"
End If
Next i

End Sub

[Artik]

I don't know if I didn't understand something?
Use with UserForm.

Sub Login2()
    Dim StartCell   As Range
    Dim Level    'As ???



    If Len(Trim(TxtUserName.Value)) = 0 Then
        MsgBox "Insert Username", vbCritical, "User Login"
        Exit Sub
    End If

    If Len(Trim(TxtPassword.Value)) = 0 Then
        MsgBox "Insert Password", vbCirtical, "User Login"
        Exit Sub
    End If


    Set StartCell = Sheets("Sheet2").Range("A2:A100").Find(TxtUserName.Value, LookIn:=xlValues, MatchCase:=False)

    If StartCell Is Nothing Then
        MsgBox "No user!", vbExclamation, "User Login"
        Exit Sub
    Else
        If TxtPassword.Value <> StartCell.Offset(0, 1).Value Then
            MsgBox "User Password is Wrong", vbCritical, "User Login"
            Exit Sub
        Else
            Level = StartCell.Offset(0, 2).Value
            MsgBox "Login Success", vbInformation, "User Login"
        End If
    End If

    MsgBox "User:" & vbTab & StartCell.Value & vbLf & _
           "Pass:" & vbTab & "OK" & vbLf & _
           "Level:" & vbTab & Level, vbInformation, "User Login"

End Sub

Artik

[amartin575]

I had it working with my original code using the form. See below. I need to do it without the form below and use For Next Loop with If Then's in the code where it shows for every row it checks the username and password using If Then. Then at the end it shows Next i for the next row.

Private Sub CmdLogin_Click()
Set WsUserName = Sheets("Table Users")
Set RgUserPas = WsUserName.Range("A2:A100")
Set c = RgUserPas.Find(TxtUserName.Value, LookIn:=xlValues, MatchCase:=False)
Password = c.Offset(0, 1).Value
Level = c.Offset(0, 2).Value
If TxtUserName.Value = "" Then
MsgBox "Insert Username", vbCritical, "User Login"
Exit Sub
End If
If TxtPassword.Value = "" Then
MsgBox "Insert Password", vbCirtical, "User Login"
Exit Sub
End If
If TxtPassword <> Password Then
MsgBox "User Name or Password is Wrong", vbCritical, "User Login"
Exit Sub
Else
MsgBox "Login Success", vbInformation, "User Login"
End If
Unload Me
End Sub

[Artik]

I still don't fully understand what exactly you want to get, so you have two macros. The first, 99 times you will be asked for your username and password. The second macro will only ask for the password for the user tested in the loop. If you press Cancel at any of the questions, the macro will exit.

Sub Test1()
    Dim WsUserName  As Worksheet
    Dim RgUserPas   As Range
    Dim c           As Range
    Dim i           As Long
    Dim strUserID   As String
    Dim strPass     As String
    Dim Level    'As ???


    Set WsUserName = Sheets("Table Users")
    Set RgUserPas = WsUserName.Range("A2:A100")


    For i = 2 To 100

OnceAgain:
        strUserID = InputBox("Enter your username:")

        If StrPtr(strUserID) Then
            If Len(Trim(strUserID)) = 0 Then
                GoTo OnceAgain
            End If

            Set c = RgUserPas.Find(strUserID, LookIn:=xlValues, MatchCase:=False)


            If c Is Nothing Then
                MsgBox "No user!", vbExclamation, "User Login"
            Else
                strPass = InputBox("Enter the password for the user " & c.Value & ":")

                If StrPtr(strPass) Then
                    
                    If strPass = c.Offset(, 1).Value Then
                        Level = c.Offset(, 2).Value
                        MsgBox "Login Success" & String(2, vbLf) & _
                               "User:" & vbTab & c.Value & vbLf & _
                               "Pass:" & vbTab & "OK" & vbLf & _
                               "Level:" & vbTab & Level, vbInformation, "User Login"

                    Else
                        MsgBox "User Password is Wrong", vbCritical, "User Login"
                    End If 'strPass = c.Offset(, 1).Value

                Else
                    'Cancel was pressed
                    Exit Sub
                End If    'StrPtr(strPass)

            End If    'c Is Nothing

        Else
            'Cancel was pressed
            Exit Sub
        End If    'StrPtr(strUserID)

    Next i

End Sub

Sub Test2()
    Dim WsUserName  As Worksheet
    Dim RgUserPas   As Range
    Dim c           As Range
    Dim i           As Long
    Dim strUserID   As String
    Dim strPass     As String
    Dim Level    'As ???


    Set WsUserName = Sheets("Table Users")
    Set RgUserPas = WsUserName.Range("A2:A100")


    For Each c In RgUserPas.Cells
        
        If Len(Trim(c.Value)) > 0 Then
            strUserID = c.Value

            strPass = InputBox("Enter the password for the user " & strUserID & ":")

            If StrPtr(strPass) Then

                If strPass = c.Offset(, 1).Value Then
                    Level = c.Offset(, 2).Value
                    MsgBox "Login Success" & String(2, vbLf) & _
                           "User:" & vbTab & c.Value & vbLf & _
                           "Pass:" & vbTab & "OK" & vbLf & _
                           "Level:" & vbTab & Level, vbInformation, "User Login"

                Else
                    MsgBox "User Password is Wrong", vbCritical, "User Login"
                End If    'strPass = c.Offset(, 1).Value

            Else
                'Cancel was pressed
                Exit Sub
            End If    'StrPtr(strPass)

        End If    'Len(Trim(c.Value)) > 0

    Next c

End Sub

Artik