Autofill Item name based on ID item in Excel VBA Userform

[liaaa]

Hello,
So I try to make a userform. The user only needs to fill in the ID item (column H) and the item name (column I) will be filled automatically. Is this possible for multiple ID ITEM in one textbox? So user will input multiple ID Items and then the Item name will be filled automatically. Or is there another alternative to do this?

This is the link for the file

Excel VBA Userform - Google Drive

drive.google.com

Thank you for your help

 

[liaaa]

yeah, i already tried it, it's working. But The input box appears for both items that have an ID and do not have an ID. I tried to input ID 317177 (this ID exists in the ITEM sheet) and ID 12345 (this ID doesn't exist in the ITEM sheet) and the input box appears twice for each ID that I input. The input box should only appear for item ID that are not in the ITEM sheet, right?

 

[Legacy 143009]

Yes,

IsError(Application.Match(IDITEM(j), Worksheets("ITEM").Range("A:A"), 0))

This means "if it can't match any items in colunm A".
Maybe you have extra spaces before or after the IDs. Check the ITEM sheet for this issue.

Also if you input ID's like 123, 456, 789, because you seperate them by only comma, they will leave extra space before numbers. What I mean is, your array will look like "123", " 456", " 789". I hope you got it. Anyway, it is easy to fix. Seperate your values only with comma, no spaces or what so ever. I am also adding a safety measure to prevent future insidances:

    If IsError(Application.Match(Trim(IDITEM(j)), Worksheets("ITEM").Range("A:A"), 0)) Then
        itemName = InputBox("Please provide Item Name for ID: " & IDITEM(j))
    Else
        itemName = Worksheets("ITEM").Cells(Application.Match(Trim(IDITEM(j)), Worksheets("ITEM").Range("A:A"), 0), "B").Value
    End If

 

[liaaa]

Hi,
Sorry for the late response. Your code works perfectly as I expected. Once again thank you very much for all your help

 

[Legacy 143009]

I am glad it did work
As I told you, don't forget to clear leading or trailing spaces in Sheet ITEM Column ID.
Also, avoid inputs other than commas in the textbox. Have a nice day