Faster way to verify data?

[jammer12001]

Im searching a list of user names and passwords. With about 15 of them it takes 30 seconds to run my code. Just wanted to check and see if there was a faster way.

Dim username, password As String
Dim ws2 As Worksheet
Set ws2 = Worksheets("employee's")
password = TextBox2.Text
username = TextBox1.Text
 
Application.ScreenUpdating = False
 
If username = ws2.Cells(2, 2) And password = ws2.Cells(2, 3) Or _
username = ws2.Cells(3, 2) And password = ws2.Cells(3, 3) Or _
username = ws2.Cells(4, 2) And password = ws2.Cells(4, 3) Or _
username = ws2.Cells(5, 2) And password = ws2.Cells(5, 3) Or _
username = ws2.Cells(6, 2) And password = ws2.Cells(6, 3) Or _
username = ws2.Cells(7, 2) And password = ws2.Cells(7, 3) Or _
username = ws2.Cells(8, 2) And password = ws2.Cells(8, 3) Or _
username = ws2.Cells(9, 2) And password = ws2.Cells(9, 3) Or _
username = ws2.Cells(10, 2) And password = ws2.Cells(10, 3) Or _
username = ws2.Cells(11, 2) And password = ws2.Cells(11, 3) Or _
username = ws2.Cells(12, 2) And password = ws2.Cells(12, 3) Or _
username = ws2.Cells(13, 2) And password = ws2.Cells(13, 3) Or _
username = ws2.Cells(14, 2) And password = ws2.Cells(14, 3) Or _
username = ws2.Cells(15, 2) And password = ws2.Cells(15, 3) Or _
username = ws2.Cells(16, 2) And password = ws2.Cells(16, 3) Then
 
Call login
Else
MsgBox "Incorrect User Information"
End If
 
Application.ScreenUpdating = True

 

[Krishnakumar]

Hi,

Try this. Untested.

Dim username, password As String
Dim ws2 As Worksheet
Dim dic As Object, k, i As Long

Set ws2 = Worksheets("employee's")
password = TextBox2.Text
username = TextBox1.Text

Application.ScreenUpdating = False
 
k = ws2.Range("b2:c16")
Set dic = CreateObject("scripting.dictionary")
    
For i = 1 To UBound(k, 1)
    dic.Item(k(i, 1)) = k(i, 2)
Next

If dic.Item(username) = password Then
    Call login
Else
    MsgBox "Incorrect User Information"
End If
Application.ScreenUpdating = True

 

[jammer12001]

Returns "incorrect user information" even when the information is correct.

 

[njimack]

Maybe this (untested)

Sub a()


Dim username, password As String
Dim ws2 As Worksheet
Set ws2 = Worksheets("employee's")
password = TextBox2.Text
username = TextBox1.Text
Dim i As Long
 
Application.ScreenUpdating = False
 
For i = 2 To 16

    If username = ws.Cells(i, 2) And password = ws.Cells(i, 3) Then
     
        Call login
Exit For
    Else
    MsgBox "Incorrect User Information"
    End If
Next i
 
Application.ScreenUpdating = True

End Sub

 

[jammer12001]

No second one didn't work. I actually ended up getting the first one to run correctly but it was just as slow as doing it the old way and I was about to type a username and then a password that was a couple lines down and log in. Maybe there isn't a better way

 

[pbornemeier]

The large If statement in your original code ran in a fraction of a second on my machine. Although I don't think any events are triggered by that code, adding Application.EnableEvents=False and Application.EnableEvents=True around a portion of your code may speed things up.

An additional thing to try would be to combine the stored and entered username and password pairs into a single string.

sCombined = TextBox2.Text & "." & TextBox1.Text

 

[Krishnakumar]

Returns "incorrect user information" even when the information is correct.

add the following line

dic.comparemode = 1

after this line of code

Set dic = CreateObject("scripting.dictionary")