Vlookup on a Userform with Textboxes

Microsoft Office 2007 enterprise
June 15, 2010 at 23:18:48
Specs: Windows Vista
Hi! I am trying to make a userform where I
enter a 6-digit code in TextBox1 and as soon
as I enter the last digit, I want its
corresponding Item Name to be displayed in
TextBox2. I already have an Excel sheet that
can be used for a Vlookup function but I do not
know how to apply it as a Visual Basic code in
the userform. How do I go about this?

See More: Vlookup on a Userform with Textboxes

June 16, 2010 at 05:16:57

Assuming that your form has a Text Box named "TextBox1" and a Label named "Label1"
the following code can be attached to the TextBox1 change event.

The code responds to each change in the text in the text box.
Whenever there are 6 characters in the text box it runs the Workbook VLOOKUP() function.
Note that the way that the range is referenced is Visual Basic style and not worksheet style.

The result of the VLOOKUP is placed as the label caption. You could apply the result to any other Forms object that accepts text.

You will need to add code to capture errors returned by VLOOKUP() such as when the text string is not found.

If the lookup table on the worksheet consists of numbers in the first column, you will have to convert the text from the text box to a number.

Private Sub TextBox1_Change()
If Len(TextBox1.Text) = 6 Then
    Label1.Caption = Application.WorksheetFunction. _
        VLookup(TextBox1.Text, Worksheets("Sheet1").Range("D2:E20"), 2, False)
End If
End Sub

For text to a number try this: VLookup(CLng(TextBox1.Text)...
Note also that if you move the lookup table on the worksheet the code will not change to follow it - the range will have to be manually altered.

Hope this helps


Report •

June 17, 2010 at 02:28:46
Thank you once again, Humar! Got it to work. :) But I'm
currently having another problem transferring the data from the
textboxes of my userform (yes, the same ones I was using
vlookup on) to the next empty row in a sheet, let's say,
"Sheet 1." I've tried all sorts of codes already, but none of
them seem to work. They always either overwrite the previous
entry, write on the wrong row or do nothing at all. Would you
happen to have any solution to this?

Thank you and sorry to keep bothering you for help.

Report •

June 17, 2010 at 04:56:57

Lets say you have some data in column B on "Sheet2" and you want to add data to the next empty row in that column, use this:
Worksheets("Sheet2").Range("B" & CStr(Application.Rows.Count)).End(xlUp).Offset(1, 0) = TextBox1.Text

Application.Rows.Count returns the last row for your version of Excel and Cstr converts the number to text (a bit of overkill as Excel will convert this on it's own - (I do this as I sometimes use the code elsewhere where there is no implicit/automatic conversion).

End(xlUp) finds the last used cell by working from the bottom up - in this case starting at the last cell in Column B on "Sheet2"

Offset(1, 0) then moves one row down (Offset parameters are number of rows to offset, number of columns to offset), so that the data is placed on the row after the last one containing data

You should be able to modify this to suit your situation. If you still have difficulty, please post the code you are trying, and say what happens when you use it.


Report •

Related Solutions

June 25, 2010 at 16:18:42
HI!! Humar.....I like this piece of code; but can't seem to make it work for me. I tried the original format from your above post and recieved runtime errors. I then posted in another excel forum and got what you see below. It's pretty much the same as your example except it has error handling. Regardless; I can never seem to find the value with the vlookup statement. Do you see anyhting wrong? I can do a vlookup formula in my workbook in a cell and have it return a correct value from the range.
Private Sub upc_Change()
If Len(upc.Text) = 12 Then
On Error Resume Next
x = Application.WorksheetFunction. _
VLookup(upc.Text, Worksheets("NEW").Range("J2:K1000"), 2, False)
If Err.Number = 0 Then
Label3.Caption = x
Label3.Caption = "No such product found"
End If
On Error GoTo 0
End If
End Sub

I wanted the person to enter the "upc" and then after the 12th digit was entered for the upc; the label 3 caption would be updated to display the product name from my vlookup table.


Report •

June 26, 2010 at 04:35:36

I can't see the data in column J on worksheet "NEW", but my guess is that the 12 digit upc codes are present as numbers, not as text.

The lookup value you are passing to VLOOKUP() is text:

If I am right about the way that the codes are listed - as numbers, not text - then converting the 12 digit text entered, to a number, should work:
VLookup(CDbl(upc.Text), Worksheets("NEW").Range("J2:K1000"), 2, False)


Report •

June 26, 2010 at 05:06:56

Alternatively, you don't need to call the Excel VLOOKUP() function.
You can do the whole search using VBA:

Private Sub upc_Change()
If Len(upc.Text) = 12 Then
    Dim rngCell As Range
    Dim blnFound As Boolean
    blnFound = False
    For Each rngCell In Range("J2:J1000")
        If rngCell.Text = upc.Text Then
            Label3.Caption = rngCell.Offset(0, 1).Text
            blnFound = True
        End If
        If blnFound = True Then Exit For
    Next rngCell
    If blnFound = False Then
        Label3.Caption = "No such product found"
    End If
End If
End Sub

As VBA is not compiled, the Visual Basic code is slower than the compiled Excel functions, but for small lookup ranges such as this, the difference is not apparent.

You will see that the comparison uses the a text value from the cell. Excel stores a text equivalent of numbers alongside the actual value and this can be accessed by rngCell.Text as opposed to rngCell.Value.

The required value is returned using the Offset() function, with zero row and one column offset arguments Offset(0, 1).


Report •

Ask Question