Vlookup function in two text box

[brendalpzm]

I have 2 text box (not a user form) in one text box the user is supposed to type the employee number (TextBox1) and when they click on the "search" button, it should bring in the TextBox2 the name according to the employee number.

The sheet where the textboxes are located is called "Input" and the data base with the employee numbers with the names is called "DB", 1st column is the emlpoyee number and 2nd is the employee name, A:B.

 

[etaf]

a lookup like vlookup or index/match should work

=Index(DB!B2:B100000 , match(A2 , DB!A2:A100000,0))

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[DanteAmor]

If they are activex controls, then

Private Sub CommandButton1_Click()
  Dim f As Range
  If Sheets("Input").TextBox1.Value <> "" Then
    Set f = Sheets("DB").Range("A:A").Find(Sheets("Input").TextBox1.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      TextBox2.Value = f.Offset(, 1).Value
    Else
      MsgBox "Employee number does not exists"
    End If
  Else
    MsgBox "Input employee number"
  End If
End Sub

If they are form controls, then

Sub SearchButton()
  Dim tb1 As Shape, tb2 As Shape
  Dim num As String
  Dim f As Range
  
  Set tb1 = Sheets("Input").Shapes("TextBox1")
  Set tb2 = Sheets("Input").Shapes("TextBox2")
  
  If tb1.TextFrame2.HasText Then
    num = tb1.TextFrame.Characters.Text
    Set f = Sheets("DB").Range("A:A").Find(num, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      tb2.TextFrame.Characters.Text = f.Offset(, 1).Value
    Else
      MsgBox "Employee number does not exists"
    End If
  Else
    MsgBox "Input employee number"
  End If
End Sub

 

[brendalpzm]

If they are activex controls, then

Private Sub CommandButton1_Click()
  Dim f As Range
  If Sheets("Input").TextBox1.Value <> "" Then
    Set f = Sheets("DB").Range("A:A").Find(Sheets("Input").TextBox1.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      TextBox2.Value = f.Offset(, 1).Value
    Else
      MsgBox "Employee number does not exists"
    End If
  Else
    MsgBox "Input employee number"
  End If
End Sub

If they are form controls, then

Sub SearchButton()
  Dim tb1 As Shape, tb2 As Shape
  Dim num As String
  Dim f As Range
 
  Set tb1 = Sheets("Input").Shapes("TextBox1")
  Set tb2 = Sheets("Input").Shapes("TextBox2")
 
  If tb1.TextFrame2.HasText Then
    num = tb1.TextFrame.Characters.Text
    Set f = Sheets("DB").Range("A:A").Find(num, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      tb2.TextFrame.Characters.Text = f.Offset(, 1).Value
    Else
      MsgBox "Employee number does not exists"
    End If
  Else
    MsgBox "Input employee number"
  End If
End Sub

this worked really well! but what if my button is an image that y copy pasted in the sheet? would that change?

 

[DanteAmor]

this worked really well! but what if my button is an image that y copy pasted in the sheet? would that change?

Use Sub SearchButton() and assign that macro to your image.
In the future, you should be more specific with the controls, if they are ActiveX or Form controls, if your button is an image or an ActiveX commmandbutton, that way you will receive a more precise answer.

 

[brendalpzm]

Use Sub SearchButton() and assign that macro to your image.
In the future, you should be more specific with the controls, if they are ActiveX or Form controls, if your button is an image or an ActiveX commmandbutton, that way you will receive a more precise answer.

the button is an image that I copy pasted from the web, maybe I need to assign the code as a macro, and the text box are from activeX controls

 

[DanteAmor]

I need to assign the code as a macro

In a module:

Sub SearchButton()
  Dim f As Range
  If Sheets("Input").TextBox1.Value <> "" Then
    Set f = Sheets("DB").Range("A:A").Find(Sheets("Input").TextBox1.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      Sheets("Input").TextBox2.Value = f.Offset(, 1).Value
    Else
      MsgBox "Employee number does not exists"
    End If
  Else
    MsgBox "Input employee number"
  End If
End Sub

 

[brendalpzm]

In a module:

Sub SearchButton()
  Dim f As Range
  If Sheets("Input").TextBox1.Value <> "" Then
    Set f = Sheets("DB").Range("A:A").Find(Sheets("Input").TextBox1.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      Sheets("Input").TextBox2.Value = f.Offset(, 1).Value
    Else
      MsgBox "Employee number does not exists"
    End If
  Else
    MsgBox "Input employee number"
  End If
End Sub

Now it works! thanks a lot!