VBA to perform VLOOKUP using textbox value input by user on Userform

[lbrunelle]

This is my first post...come to the site a lot and find the community very helpful and knowledgeable. Hoping someone will see this and be able to help me with my code. Rather new to VBA.

CASE

1. I want users of the Userform to enter their employee ID number in TextBox2.
2. Based on the TextBox2 (Employee ID Number), I want to do a vlookup to populate other textboxes on the userform.
3. The values of the other textboxes will eventually go into specific cells of the worksheet.
4. There will only be one entry per form (eligible employees will receive a form to complete) - so I don't need to have VBA/excel find first or last rows.

TROUBLE/ISSUE
When I test, I can enter the employee ID number, but when I hit tab...nothing is populating in Textbox 5. I would like to use the same reference to populate two or three other textboxes.

Here's where I think I'm stuck - see code below. Textbox2 is employee ID; Textbox 5 is the first value I want to be returned and entered when the vlookup is performed based on the value of Textbox2 (employee ID) entered by the user.

All of my code and userform are Sheet 1. The lookup range (data) is in the same workbook as Sheet 2 (but Sheet 2 will be hidden when this is deployed). Thanks in advance for your help and expertise!

Private Sub TextBox2_LostFocus()
If TextBox2.Value <> "" And Not IsNumeric(TextBox2) Then
    MsgBox "Please enter Numbers only"
ElseIf TextBox2.Value < 4 Or TextBox2.Value > 6 Then
    MsgBox "Invalid entry. Stantec employee ID number is between ""4"" and ""6"" numbers."
ElseIf Not IsEmpty(TextBox2.Text) Then
    TextBox5.Text = Application.WorksheetFunction.VLookup(TextBox2.Text, Sheets("Sheet2").Range("A1:N8240"), 10, False)
End If
End Sub

All of my code is below. NOTE: The functionality of the userform changed after my first demo to managers, and now I'm building in the VLOOKUPS. I have not yet made changes to the rest of the VBA (previous version was just having employee's enter information into Userform that would be put into specific cells of the active worksheet).

Private Sub CommandButton1_Click()
If OptionButton1.Value = True Then


Sheets("Sheet1").Range("K31").Value = TextBox5.Text
Sheets("Sheet1").Range("K32").Value = TextBox5.Text


End If


If OptionButton2.Value = True Then


Sheets("Sheet1").Range("K31").Value = TextBox5.Text
Sheets("Sheet1").Range("K32").Value = TextBox7.Text
Sheets("Sheet1").Range("K35").Value = TextBox6.Text


End If


If OptionButton3.Value = True Then


Sheets("Sheet1").Range("K31").Value = TextBox5.Text
Sheets("Sheet1").Range("K32").Value = TextBox7.Text


End If


If OptionButton4.Value = True Then


Sheets("Sheet1").Range("K42").Value = TextBox5.Text
Sheets("Sheet1").Range("K43").Value = TextBox5.Text


End If


End Sub


Private Sub TextBox1_Change()
Sheets("Sheet1").Range("D25").Value = TextBox1.Text
TextBox1.Text = Application.WorksheetFunction.VLookup(TextBox2.Text, Sheets("Sheet2").Range("A1:N8240"), 2, False)
End Sub


Private Sub TextBox3_Change()
Sheets("Sheet1").Range("I25").Value = TextBox3.Text
TextBox3.Text = Application.WorksheetFunction.VLookup(TextBox2.Text, Sheets("Sheet2").Range("A1:N8240"), 11, False)
End Sub


Private Sub TextBox4_Change()
Sheets("Sheet1").Range("I26").Value = TextBox4.Text
TextBox4.Text = Application.WorksheetFunction.VLookup(TextBox2.Text, Sheets("Sheet2").Range("A1:N8240"), 14, False)
End Sub


Private Sub TextBox2_LostFocus()
If TextBox2.Value <> "" And Not IsNumeric(TextBox2) Then
    MsgBox "Please enter Numbers only"
ElseIf TextBox2.Value < 4 Or TextBox2.Value > 6 Then
    MsgBox "Invalid entry. Stantec employee ID number is between ""4"" and ""6"" numbers."
ElseIf Not IsEmpty(TextBox2.Text) Then
    TextBox5.Text = Application.WorksheetFunction.VLookup(TextBox2.Text, Sheets("Sheet2").Range("A1:N8240"), 10, False)
End If
End Sub


Private Sub TextBox2_Change()


End Sub


Private Sub TextBox5_Change()
Sheets("Sheet1").Range("K31").Value = TextBox5.Text
End Sub


Private Sub TextBox8_Change()
Sheets("Sheet1").Range("B48").Value = TextBox8.Text
End Sub


Private Sub CommandButton2_Click()
Me.Hide
End Sub