Solved: How to check the values stored in excel sheet thru VBA

[shahcu]

Hi!

I have made a VB form that will accept user ID and password.

This will check the ID and password with the list of users stored in excel sheet2 of a workbook...

if the user ID and password is correct then it will open another form in which it shows the user name in first text box available....

Here is the code...


Sub FindIt()

Dim uid As String
Dim pwd As String
Dim agname As String
Dim i As Integer
uid = TextBox1.Value
pwd = TextBox2.Value

ActiveWorkbook.Activate
Sheets("Sheet2").Select

If StrComp(Sheet2.Cells(2, 1), uid) = 0 And StrComp(Sheet2.Cells(2, 2), pwd) = 0 Then
agname = Sheet2.Cells(2, 3)
UserForm1.TextBox9.Value = agname
UserForm1.Show

Else

MsgBox "Please enter Valid User ID and Password"
End If
UserForm2.TextBox1.Text = ""
UserForm2.TextBox2.Text = ""

End Sub

Issue is it will only check the value of user iD and password in one particular row... what i need is it shud check the value of User ID and pwd in entire Column A and Column B.
Column a contains User IDs and Column b contains Pwd..

Pls help

[Bob Phillips]

Sub FindIt()
Dim uid As String
Dim pwd As String
Dim agname As String
Dim i As Integer
Dim iPos As Long

uid = TextBox1.Value
pwd = TextBox2.Value

ActiveWorkbook.Activate
Sheets("Sheet2").Select

On Error Resume Next
iPos = Application.Match(uid, Sheet2.Columns(1), 0)
On Error GoTo 0
If iPos > 0 And StrComp(Sheet2.Cells(iPos, 2), pwd) = 0 Then
agname = Sheet2.Cells(2, 3)
UserForm1.TextBox9.Value = agname
UserForm1.Show

Else

MsgBox "Please enter Valid User ID and Password"
End If
UserForm2.TextBox1.Text = ""
UserForm2.TextBox2.Text = ""

End Sub

[shahcu]

Hi!

Thanx a lot for the solution. it resolve the partial problem...

that is If i enter the user ID and password which is already stored in excel sheets (in whatever column)it works fine. However when I am entering any fake value then instead of showing "Please enter Valid User ID and Password" error message it says "Run Time error '1004';
Application defined or object defined error at line

If iPos > 0 And StrComp(Sheet2.Cells(iPos, 2), pwd) = 0 Then

pls help..

Once again .. Thanx a lot.

[shahcu]

Thanx a lot for ur reply it resolves problem partially...

Now it allows me to check the user Id and password stored in any row in worksheet.. however when i m trying to enter any fake uid or pwd it says "Run Time error '1004' Application defined or Object defined error at line
If iPos > 0 And StrComp(Sheet2.Cells(iPos, 2), pwd) = 0 Then

pls help
Once again thanx a lot...

[Bob Phillips]

Sorry, my bad



Sub FindIt()
Dim uid As String
Dim pwd As String
Dim agname As String
Dim i As Integer
Dim iPos As Long

uid = TextBox1.Value
pwd = TextBox2.Value

ActiveWorkbook.Activate
Sheets("Sheet2").Select

On Error Resume Next
iPos = Application.Match(uid, Sheet2.Columns(1), 0)
On Error GoTo 0
If iPos > 0 Then
If StrComp(Sheet2.Cells(iPos, 2), pwd) = 0 Then
agname = Sheet2.Cells(2, 3)
UserForm1.TextBox9.Value = agname
UserForm1.Show
Exit Sub
End If
End If

MsgBox "Please enter Valid User ID and Password"
UserForm2.TextBox1.Text = ""
UserForm2.TextBox2.Text = ""

End Sub

[shahcu]

Thank u very much..

It did the trick ...

Regards,

[shahcu]

Hi!

Although the above code resolves the issue however it is not doing one thing properly..

Under this section:
If iPos > 0 Then
If StrComp(Sheet2.Cells(iPos, 2), pwd) = 0 Then
agname = Sheet2.Cells(2, 3)
UserForm1.TextBox9.Value = agname
UserForm1.Show
Exit Sub
End If

Line .......
agname = Sheet2.Cells(2, 3)

Here the value that is being picked is always the first one... When I enter any user ID or password if it is correct it always stored the first Agent name value instead of the correct user id and passowrd value..

So, please help

[Bob Phillips]

Sub FindIt()
Dim uid As String
Dim pwd As String
Dim agname As String
Dim i As Integer
Dim iPos As Long

uid = TextBox1.Value
pwd = TextBox2.Value

ActiveWorkbook.Activate
Sheets("Sheet2").Select

On Error Resume Next
iPos = Application.Match(uid, Sheet2.Columns(1), 0)
On Error GoTo 0
If iPos > 0 Then
If StrComp(Sheet2.Cells(iPos, 2), pwd) = 0 Then
agname = Sheet2.Cells(iPos, 3)
UserForm1.TextBox9.Value = agname
UserForm1.Show
Exit Sub
End If
End If

MsgBox "Please enter Valid User ID and Password"
UserForm2.TextBox1.Text = ""
UserForm2.TextBox2.Text = ""

End Sub

[shahcu]

Thanx.

Done..

I appreciate ur time and support.