Userform & VLOOKUP to populate fields

ccollier

New Member
Joined
Jun 21, 2011
Messages
2
I am new to Userforms, but have successfully created one where all entries work - except one. I am creating a Userform for a student directory. If I create a user as NEW, it works fine. However, I have information from previous years that just needs to be updated, not NEW. This information resides in a workbook tab named "Combine".

User form is set up like this:
Text Box - txtstudent_number
- user will enter Student Number
Command Box - cmd_lookup_studentID
- user will CLICK button to look up information in Excel workbook "Combine"
If the txtstudent_number makes a match in column A in "Combine", pull First Name, Last Name, Address, City, State, Zip into workbook.
- user can update the information and then click update.

I have done lots of searches to try to create the code for the Command Box, with no luck. This is what I have today to bring in the firstname (understanding that I will need to add code to bring in the other fields):

Command Box - cmd_lookup_studentID CODE:

Private Sub cmd_lookup_studentID_Click()
Dim Studentnumber As String
Studentnumber = txtstudent_number
Dim Range As Range
Set Range = Worksheets("Combine").Range("A1:AE1")
txtstudent_firstname.Value = Application.WorksheetFunction.VLookup(Studentnumber, Range, 2, 0)

End Sub
 

ccollier

New Member
Joined
Jun 21, 2011
Messages
2
I'm still trying to work through this. I've searched through lots of info and changed up the code. Still not working. I'm not sure why the VLOOKUP code isn't right. Any assistance is appreciated.


CODE:


Private Sub cmd_lookup_studentID_Click()
Dim Range As Range, ID As Range, res As Variant
Set Range = Worksheets("Combine").Range("A3:AI500").Columns(35)
Student_Number = Application.Match(txtstudent_number.Text, Range, 0)
If Not IsError(res) Then
Set ID = Range(res)
txtstudent_firstname.Text = Application.VLookup(Student_Number, Range, 2, 0)
txtstudent_lastname.Text = Application.VLookup(Student_Number, Range, 3, 0)
Else
MsgBox "Not found in database"
End If
End Sub
 

Forum statistics

Threads
1,082,139
Messages
5,363,362
Members
400,730
Latest member
cookie123

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top