Userform VLookup not working

[srschicago]

Hi guys


I want to enter a PartID(text) in userform TextBox1 and have the userform display an alternative PartID in TextBox2 and description in TextBox3. Sheet1 has PartID1, PartID2, and Description in column A, B, and C respectively. I have this code below which appears to have worked for someone else. But when I enter text into TextBox1, nothing updates/loads in TextBox2 & TextBox3. Why?

Private Sub PartID1_Change()


    PartID2 = Application.VLookup(TextBox1.Value, Range("A1:C10000"), 2, 0)
    TextBox2.Value = PartID2
    Description = Application.VLookup(TextBox1.Value, Range("A1:C10000"), 3, 0)
    TextBox3.Value = Description
    
End Sub

 

[bobsan42]

Because the event this code responds to is different.
This event will trigger when you make a change to the value of a control named PartID1.
You need do change the event that triggers the code. Enter this in you userform code module:

Private Sub TextBox1_AfterUpdate()
    TextBox2.Value = Application.WorksheetFunction.VLookup(TextBox1.Value, Range("A1:C10000"), 2, 0) 'PartID2
    TextBox3.Value = Application.WorksheetFunction.VLookup(TextBox1.Value, Range("A1:C10000"), 3, 0) 'Description
End Sub

If you use the Change event the code will trigger on every key stroke which changes the contents of the textbox. AfterUpdate will only trigger once - after you have completed the change (after you press Enter or exit the TextBox)

 

[srschicago]

Thanks bobsan42. I applied your code change and there is still not any change when I enter something in TextBox1 and press enter or exit the textbox. Is there something about how or where I enter the Private Sub code perhaps?

Note. This is the only code I am running except the userform.show macro. Am I missing something?

 

[srschicago]

Got it!. I had the code in the wrong module. I needed to put it in the Form code module. It works perfectly now. Thanks.

 

[bobsan42]

You're welcome.