vlookup in a text box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jeffbert - 22 Jun 2006 16:53 GMT

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.

Reply to this Message

aidan.heritage@virgin.net - 22 Jun 2006 17:07 GMT

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!

> 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
[quoted text clipped - 7 lines]
> Text box 2 looks up the value of text box 1 in a table and returns the value
> in column 2.

Reply to this Message

Chip Pearson - 22 Jun 2006 17:08 GMT

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.

Signature

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

>I have a textbox that shows a number. I need another text box on
>the same
[quoted text clipped - 10 lines]
> returns the value
> in column 2.

Reply to this Message

jeffbert - 22 Jun 2006 17:43 GMT

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

> Use code like the following in the sheet's code module. Change
> the lookup range to suit your needs.
[quoted text clipped - 27 lines]
> > returns the value
> > in column 2.

Reply to this Message

jeffbert - 28 Jun 2006 14:43 GMT

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

Jeff

> Chip
>
[quoted text clipped - 52 lines]
> > > returns the value
> > > in column 2.

Reply to this Message