vlookup in a text box
 

I have a textbox that shows a number. I need another text box on the same
sheet to lookup that number and show the corresponding description that
relates to that number.

EX:

974 - (Text Box 1)
Bob - (Text box 2)


Text box 2 looks up the value of text box 1 in a table and returns the value
in column 2.

vlookup in a text box
 

Don't try to make your life so difficult - in a hidden area of the
worksheet, have a 2 cells to do the work - the FIRST textbox is set to
be linked to the first cell. The second cell contains the lookup
formula. The First Textbox is set so that on the Exit event it sets
the text of the Second textbox to the value of the second cell (hope
that's clear!). DON'T link the second cell though, as it would change
the formula to the value - which isn't what you want!


jeffbert wrote:
I have a textbox that shows a number. I need another text box on the same
sheet to lookup that number and show the corresponding description that
relates to that number.

EX:

974 - (Text Box 1)
Bob - (Text box 2)


Text box 2 looks up the value of text box 1 in a table and returns the value
in column 2.

vlookup in a text box
 

Use code like the following in the sheet's code module. Change
the lookup range to suit your needs.

Private Sub TextBox1_KeyUp(ByVal KeyCode As
MSForms.ReturnInteger, ByVal Shift As Integer)
Dim Res As Variant
If KeyCode = vbKeyReturn Then
Res = Application.VLookup(Me.TextBox1.Text,
Range("A1:B5"), 2, False)
If IsError(Res) = False Then
Me.TextBox2.Text = Res
End If
End If
End Sub

It will run the lookup when you press the Enter key.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"jeffbert" wrote in message
...
I have a textbox that shows a number. I need another text box on
the same
sheet to lookup that number and show the corresponding
description that
relates to that number.

EX:

974 - (Text Box 1)
Bob - (Text box 2)


Text box 2 looks up the value of text box 1 in a table and
returns the value
in column 2.

vlookup in a text box
 

Chip

Becuase the textbox is on a sheet that is all calculations, i am enabling
this macro when the worksheet is selected. Therefore I have changed it to a
worksheet activate event.

I can't seem to get it to work. Does it matter that the values in the text
box are "text", and the table it is looking up the value to is a "number"?
Listed below is what I have so far. (The range is defined as BuyerTable)

Thanks


Private Sub Worksheet_Activate()

Dim Res As Variant
Res = Application.VLookup(Me.Buyer_Num_Margo.Text,
Worksheets("Validation tables").Range("BuyerTable"), 2, False)
If IsError(Res) = False Then
Me.Buyer_Name_Margo.Text = Res
End If

End Sub



Jeff


"Chip Pearson" wrote:

Use code like the following in the sheet's code module. Change
the lookup range to suit your needs.

Private Sub TextBox1_KeyUp(ByVal KeyCode As
MSForms.ReturnInteger, ByVal Shift As Integer)
Dim Res As Variant
If KeyCode = vbKeyReturn Then
Res = Application.VLookup(Me.TextBox1.Text,
Range("A1:B5"), 2, False)
If IsError(Res) = False Then
Me.TextBox2.Text = Res
End If
End If
End Sub

It will run the lookup when you press the Enter key.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"jeffbert" wrote in message
...
I have a textbox that shows a number. I need another text box on
the same
sheet to lookup that number and show the corresponding
description that
relates to that number.

EX:

974 - (Text Box 1)
Bob - (Text box 2)


Text box 2 looks up the value of text box 1 in a table and
returns the value
in column 2.

vlookup in a text box
 

I had to change Me.Buyer_Num_Margo.Text to Val(Buyer_Num_Margo) and
everything worked fine. Thanks again for the help!

Jeff


"jeffbert" wrote:

Chip

Becuase the textbox is on a sheet that is all calculations, i am enabling
this macro when the worksheet is selected. Therefore I have changed it to a
worksheet activate event.

I can't seem to get it to work. Does it matter that the values in the text
box are "text", and the table it is looking up the value to is a "number"?
Listed below is what I have so far. (The range is defined as BuyerTable)

Thanks


Private Sub Worksheet_Activate()

Dim Res As Variant
Res = Application.VLookup(Me.Buyer_Num_Margo.Text,
Worksheets("Validation tables").Range("BuyerTable"), 2, False)
If IsError(Res) = False Then
Me.Buyer_Name_Margo.Text = Res
End If

End Sub



Jeff


"Chip Pearson" wrote:

Use code like the following in the sheet's code module. Change
the lookup range to suit your needs.

Private Sub TextBox1_KeyUp(ByVal KeyCode As
MSForms.ReturnInteger, ByVal Shift As Integer)
Dim Res As Variant
If KeyCode = vbKeyReturn Then
Res = Application.VLookup(Me.TextBox1.Text,
Range("A1:B5"), 2, False)
If IsError(Res) = False Then
Me.TextBox2.Text = Res
End If
End If
End Sub

It will run the lookup when you press the Enter key.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"jeffbert" wrote in message
...
I have a textbox that shows a number. I need another text box on
the same
sheet to lookup that number and show the corresponding
description that
relates to that number.

EX:

974 - (Text Box 1)
Bob - (Text box 2)


Text box 2 looks up the value of text box 1 in a table and
returns the value
in column 2.