Solved: Login / Password validation - limited attempts?

[debauch]

Hey all,
I have some basic code, that requires users to input a login and password, to use certain parts of an application. It works just fine, but I am wondering it there is a way to only allow a limited amount of tries (maybe 3?) and if they incorrectly input info 3 times, it will kick them out, or notify some1 etc.

Here is the vba I am using to validate the user :

[VBA]
Private Sub CommandButton1_Click()
'validate username / password

' (user1)
If Range("userDB!b2") = txtLogin.Value And Range("userDB!c2") = txtPassword.Value Then
UserForm2.Show
Else: MsgBox ("Incorrect Login and/or Password")
End If

(user2)
[ code repeats its self for user 2]
End Sub
[/VBA]

Also, is it possible to loop through the users rather than me tryping if/then's for each user? Thanks in advance.

[asingh]

you could try the following:

Private Sub CommandButton1_Click()
'validate username / password
Dim IncorrectCounter as string
IncorrectCounter = Range("userDB!d2").value

If IncorrectCounter < 3 then
' (user1)
If Range("userDB!b2") = txtLogin.Value And Range("userDB!c2") = txtPassword.Value Then
UserForm2.Show
else
MsgBox ("Incorrect Login and/or Password")
IncorrectCounter = IncorrectCounter + 1
Range("userDB!d2").value = IncorrectCounter
End If
else
MsgBox ("Cannot Try more than 3 logins")
'put the rest of code..to shut down log in procedure.

end if

End Sub

[debauch]

Works perfect, thank-you.

What about looping through users until it reaches the end ? This is what I have so far, but it doesnt do anything , lol ...

[VBA]
Dim i As Integer
Dim intRowCount As Integer
intRowCount = Range("b2").Count And Range("c2").Count = -1
For i = 1 To intRowCount
Next i
[/VBA]

I am not very good w/ loops, but I would want it to work sometihng similar to that. Thanks again.

[Killian]

Hi there

Here's an approach:

Name the range of users' names, "usernames"
Now just to test the names, you can loop through the cells of that range[VBA]Dim c As Range
Dim ValidateUser As Boolean

For Each c In Range("usernames").Cells
If c.Text = txtLogin.Value Then
ValidateUser = True
Exit For
End If
Next c
If ValidateUser = True Then
UserForm2.Show
End If[/VBA]Now if the user's passwords are in the next column, you can check the offset (one column over) of each range for the password on each pass.

This is a general techique for looping through any collection. Because you're working with a worksheet range, a better technique would be using "Find" which will be much faster - useful if you have a lot of users.[VBA]Dim c As Range

Set c = Range("usernames").Find(txtLogin.Value)
If Not c Is Nothing Then
If c.Offset(0, 1).Text = txtPassword.Value And _
c.Text = txtLogin.Value Then
Set c = Nothing
UserForm2.Show
End If
End If[/VBA]

[debauch]

hmm...I like the second approach you listed , I will check it out. Sounds like a solved thread to me though Thanks.

[debauch]

Killian,
what does

[VBA]
Set c = Nothing
[/VBA]
represent?

[debauch]

I got it, everything is working. Thanks for all your input guys.

[Killian]

At that point, userform1 is still loaded. Setting the range variable to nothing ensures that c is tidied up (since it contains the range of a valid login) as soon as we're done with it.
Maybe not essential as it stands, it's only a few lines of code, but when it all goes in to make up a larger program, it's good programming practice - if we come back to modify the code later, we don't get caught out by using variables that we're expaecting to be empty.

Speaking of modifying code, I forgot to add the failure message and the login attempts:
you could add a textbox named "lblMessage" to the form and display the user feedback that way.

It might be better to wrap the login check in a function so here's the revisions done that way[VBA]Option Explicit

Dim lngAttempts As Long

Private Sub CommandButton1_Click()

If ValidLogin(txtLogin.Value, txtPassword.Value) Then
lngAttempts = 0
UserForm2.Show
lblMessage.Caption = "Username: " & txtLogin.Value
Else
txtLogin.Value = ""
txtPassword.Value = ""
lblMessage.Caption = "Login failed!"
End If
lngAttempts = lngAttempts + 1
If lngAttempts = 3 Then
lblMessage.Caption = "Locked: 3 failed login attempts"
txtLogin.Enabled = False
txtPassword.Enabled = False
CommandButton1.Enabled = False
End If

End Sub

Private Function ValidLogin(strName As String, strPW As String) As Boolean

Dim c As Range

Set c = Range("usernames").Find(strName)
If Not c Is Nothing Then
If c.Text = strName And c.Offset(0, 1).Text = strPW Then
Set c = Nothing
ValidLogin = True
End If
End If

End Function[/VBA]

[debauch]

sweet. Thank you. This way, people cannot just sit there, and guess passwords forever.

[debauch]

hmm...something is not working. I have attached a sample file you could look at ?

[Killian]

Your range includes empty cells, which we need to filter out by wrapping the Find function in an If...Then

I also neglected to check this properly, we need to make the find lookat the whloe cell text or someone can put in part of a valid login and succeed![VBA]Private Function ValidLogin(strName As String, strPW As String) As Boolean
Dim c As Range

If strName <> "" And strPW <> "" Then
Set c = Range("usernames").Find(What:=strName, LookAt:=xlWhole)
If Not c Is Nothing Then
If c.Text = strName And c.Offset(0, 1).Text = strPW Then
Set c = Nothing
ValidLogin = True
End If
End If
End If
End Function[/VBA]

[debauch]

Perfect.
This works beautifully. You should have this as a KB entry. Thanks again. All aspects I tested, and everything works.