Validating Item Numbers

[Julez80s]

Good Day Forum,

I come to you to ask for your help solving the following problem:

I have a simple userform that will help the user enter a list of items into a worksheet. The user would select a group from a combobox (groups are: breads, meats, cheeses, veggies) then use a textbox to enter the item number. Each group has about 20 or so items, each with a unique item number. The problem that I need your help solving is that I need a way to validate the item number being enter into the textbox so that the user doesn't enter a number that doesn't belong to that group. In other words... how can I stop the user from entering a number that doesn't belong to the group selected in the combobox?

I was hoping to check the item number in an _AfterUpdate() event for the textbox but I don't know where or how to start the validation process for the item number.

Any help you can provide will be appreciated!

And thanks for your time!

Julian

[Julez80s]

Hello again...

I've been trying to use a vlookup function to see if I could validate the item number on the form but I'm getting stuck... the number is not being validated at all. I'm attaching a sample of the file I'm working on to see if anyone of you can tell me what I did wrong... or what I'm missing that is causing the problem. Please let me know if anyone has any suggestions or comments that would help find a solution.

And thanks again for any help anyone can provide.

Julian

[Solus Rankin]

Does it have to be a textbox? perhaps another ComboBox that only contains items from the 'category' that is chose?

Edit:

Comboboxes can be setup to be drop down only or have the ability for someone to type into them.

[Leith Ross]

Hello Julez80s,

Have a look at the attached workbook.

[Solus Rankin]

Test1 (1).xlsm

Not completely tested.

[Julez80s]

Thank you both for the replies...

XeRo,
I thought about making a listbox or another combobox, as you suggested, but the problem with that is the item list will keep growing, and searching for the item number to select it will take longer than actually typing it in. The purpose of the form is to let the user finish the data input as fast and as accurately as possible. The file you posted doesn't seem to do anything... other than what I had been dealing with. When I choose an item from the Group combobox and enter the Item Number the same problem happens and the number doesn't get validated; the form either stops with the warning message or it doesn't do anything at all.

Leith,
The file you posted brings up a lot of empty boxes or what looks like part of the spreadsheet inside the listbox when I select one of the items from the Group combobox, but the item that I'm able to select from the listbox is not a number but text... and it doesn't get validated either.

Another problem with the combobox idea is that if the user is able to type the number in the field then the user could type the wrong item number as well... there would have to be a way to validate the item number against the group.

Again, thank you both for taking the time to look at my problem... and please let me know if you have any other ideas on this one.

Julian

[Solus Rankin]

The combo boxes I included on my solution remove all items once a new category has been selected. Test it and see if you think that will still be a problem.

[Julez80s]

Sorry Xero... maybe I'm looking at a different file, but the one posted under your name doesn't have any comboboxes at all... could you take a look at the file and let me know?

Thanks!

[Solus Rankin]

My mistake. I attached the wrong file. Here is the correct one.

I also added a close button to be able to close the userform a little easier.

Test1.xlsm

[Julez80s]

XeRo,

Thank you for the file... and the solution; I can use it to get me started and wait to see if the list length doesn't become a problem later on. I had to make a couple of changes to your solution but it's working great.

Thanks again!

Julian