Home/ Questions/Q 3713
Next
Answered
computing
  • 3
Asked: 2021-11-04T21:23:52+00:00 In:

Vlookup On a Userform With Textboxes

  • 3

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?

Share

    1 Answer

    1. Best Answer
      Hi,

      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

      Regards

      • 0