Autofill Textbox

[JorgenKjer]

I have a woorkbook with a Sheet with 3 columns, column A is Employee numbers, column B is Names of Employees and column C is Phone numbers that row by row matches the Employee number.

In a User Form, I have 3 Text boxes.

If I write an Employee number in text box 1, it is then possible to make a VBA code filling in resp. Text box 2 with the name of Employee corresponding to the Employee number and in Text box 3 the telephone number?

I hope the question makes sense and will appreciate if anyone will try to help.

Thanks in advance.

Yours sincerely

Jorgen Kjer

 

[Saurabhj]

Hi,

Use below code in userform.
Type the id in text field and press tab button.

• When cursor exit from text field, corresponding name and phone number will be populated.
• If Id not found, the corresponding text fields would be blank.

Private Sub txtEmpID_AfterUpdate()
    Dim id As Integer, name As String, phone As String
    Dim foundcell As Range
    id = txtEmpID.Value
    
    With Sheets("Sheet1")
    Set foundcell = .Cells.Find(what:=id, after:=.Cells(1, 1), LookIn:=xlValues, _
    lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, _
    MatchCase:=False, searchformat:=False)
    
  
    If Not foundcell Is Nothing Then
        txtEmpName.Value = .Cells(foundcell.Row, 2)
        txtPhone.Value = .Cells(foundcell.Row, 3)
    Else
        txtEmpName.Value = ""
        txtPhone.Value = ""
    End If
        End With
End Sub

 

[mohadin]

Hi
Try

Private Sub TextBox1_AfterUpdate()
Dim i As Long
    For i = 2 To Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
        If Sheets("Sheet1").Cells(i, 1).Text = Me.TextBox1.Value Then
            Me.TextBox2 = Cells(i, 2)
            Me.TextBox3 = Cells(i, 3)
        End If
    Next
End Sub

 

[JorgenKjer]

Hi,

Use below code in userform.
Type the id in text field and press tab button.

• When cursor exit from text field, corresponding name and phone number will be populated.
• If Id not found, the corresponding text fields would be blank.

Private Sub txtEmpID_AfterUpdate()
    Dim id As Integer, name As String, phone As String
    Dim foundcell As Range
    id = txtEmpID.Value
   
    With Sheets("Sheet1")
    Set foundcell = .Cells.Find(what:=id, after:=.Cells(1, 1), LookIn:=xlValues, _
    lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, _
    MatchCase:=False, searchformat:=False)
   
 
    If Not foundcell Is Nothing Then
        txtEmpName.Value = .Cells(foundcell.Row, 2)
        txtPhone.Value = .Cells(foundcell.Row, 3)
    Else
        txtEmpName.Value = ""
        txtPhone.Value = ""
    End If
        End With
End Sub

Hi Saurabhj

Thanks for the help, the code works fine if the ID numbers are in sequence. Example

16188
16189
16190
16191
16192
16193
16194
16195
16196
16197

But if the ID numbers, for example, are as show

16188
38331
39733
45827
50035
50036
51592
52746
71381
17071381

I get an error and since I'm not an expert in VBA I have not been able to figure out what is going wrong
Maybe you have an idea.

Yours sincerely
Jorgen Kjer

 

[JorgenKjer]

Hi
Try

Private Sub TextBox1_AfterUpdate()
Dim i As Long
    For i = 2 To Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
        If Sheets("Sheet1").Cells(i, 1).Text = Me.TextBox1.Value Then
            Me.TextBox2 = Cells(i, 2)
            Me.TextBox3 = Cells(i, 3)
        End If
    Next
End Sub

Hi Mohadin

Many thanks for the help your code works perfectly

Yours sincerely
Jorgen Kjer

 

[mohadin]

@JorgenKier
You are very welcome
And thank you for the feedback
Be happy and safe