February 14, 2014

Compare UserForm Data with Worksheet Data

How to compare both the login and registration data in Excel to verify a user

In the last video we created a kind of ‘registration‘ form with two labels, two text boxes and a command button. The user entered his name and password in the text boxes, clicked on the command button and the data was captured in appropriate worksheet cells. Now what-if we create a ‘login’ form and wish that the user enters his name password, this data is compared with the Excel worksheet data and if both the name and the password match, we let him visit our website called ‘http://www.familycomputerclub.com’. How can we do this sort of thing using a form and appropriate VBA coding?

We use a looping process to compare the name and password entered by the user in the user-form with  all the entries in our Excel worksheet. If the entered user-name and password match with data in the Excel worksheet then we let the user visit our website else we give out a message like ‘user-name or password invalid’.

Here’s the complete VBA code for the command button to perform the task:

Private Sub CommandButton1_Click ()
‘we initialize the row number where we will start comparing the data because the worksheet has headers in row 1
‘Now we start the looping process
Do While Cells(Row,1).Value<>””
‘We compare the entered data with the worksheet data
If Cells(Row,1).Value=TextBox1.Text And Cells(Row,2).Value=TextBox2.Text then
ActiveWorkbook.FollowHyperlink  “http://www.familycomputerclub.com”

‘we go to the next row to compare data
End If

‘If the user is not found based on his inputs in the login form, we give out a message
MsgBox (“Invalid user name or password”)

End Sub

Watch the video:

2 thoughts on “Compare UserForm Data with Worksheet Data

  1. How would I specify the code above to look for the user name and password data in a particular sheet ?

  2. 1= Sr इसमें वेब लिंक के स्थान पर कंप्यूटर के c ड्राइव के फाइल को 2ओपन करने का क्या कोड होगा .
    2= लॉग इन user का नाम व् लॉग इन टाइम उसी वर्कबुक के शीट 2 पर एंट्री का कोड क्या होगा

Comments are closed.