Searching for text matches and showing results in ComboBox

I have been searching the forum for this code but without success (sorry in advance I am sure it must be here somewhere). I need a simple partial match text search and select from results function/code.

I have a table with 1000+ rows of products. Column 3 has the user meaningful product description. I would like to use a combobox where the user types in some text and on pressing return the combobox lists all rows that contain the text as part of the field in column 3. The user then looks down the list and selects a product. On selection I just need the row number so I can populate a userform with the data from that row.

Example with 6 columns (excuse the formatting):

1103 , Moda Pre-cuts , Moda Bella Solids Natural 9900-12 Jelly Roll , 6 0 0
1101 , Moda Pre-cuts , Moda Bella Solids White 9900-98 Jelly Roll , 1 0 0
1086 , Clearance , PB&J Creamy Red Floral 30322-11 , 2 0 0
1060 , Moda , Spectrum Pistachio 2000-G60 , 66 66 66
1066 , Moda , Spectrum Steel 2000-S62 , 14 0 0
1067 , Moda , Spectrum White 2000-T63 , 43 0 0
1068 , Moda , Spectrum Vanilla 2000-Q60 , 56 0 0


User searches for "White" and the combobox has 2 rows taken from column 3 containing; "Spectrum White 2000-T63" , "Moda Bella Solids White 9900-98 Jelly Roll" . The user selects Spectrum white in the combobox and I get back row number 6.


Hope this makes some sense. Thanks in advance.

Tony

Ok Tony Look at this first.

Then come back to me

the three text boxes are for data entry.

enter dal into one and r into one of the others.

Hi. Thank you so much for getting me the code. I have been trying to work with it today. However I am not having much success adapting it.

I have got it to find the first occurrence of the string but cant get the combobox to get filled.

I have attached my working spreadsheet with only the userform left in it. I would be very grateful if you could take a quick look at it. I guess it must be something simple.

Also I noticed when I launch the userform the focus is on the combobox rather than the text box. Not sure what causes that to happen as it doesn't in your code.

Many thanks

Tony

You decided to use a Combobox

This behaves a bit differently.

I have maodified it so it works

Wow. That was quick. You are very kind. I thought the combobox was the only way to make it work? It is fine.

It looks like it is returning duplicate records - not sure why. I entered "Spec" and it returned 8 records which was actual 4 unique records but 2 copies of each. The search is actually only on values in column C as the other fields are meaningless to the user.

Just to check; Once the user selects an item from the combobox. I need to know the corresponding row in the spreadsheet. Is the row value returned in ListBox1.Value?

Thank you once again

Tony

Sorry. Just realised you used a list box rather than combobox. I will change to that and see if I get better results. Thanks again

Got it all sorted apart from 1 thing. I included the Locator£ ReLocator functions (sorry - I don't know what they do). On the ListBox1_Click() function I have included them at the start and finish. However the Relocator function fails and goes to debug when called. Can I remove it? I attached the s/s so you can see. Many thanks Tony

Hi Sorted now. I found the public declarations. Does it matter where I put these in the code "Public ASht, ACell As String". Many thanks Tony

Hum. I was hoping to have the user form prepopulated with data from a row when the user double clicks on the row. I had a different form that did all the population. It works fine. When I add the search functions unfortunately it goes into a loop. I guess it think the initialise user form function is a change to the text box so it goes around in a loop running the search function. Sorry to be keep asking for help but could you point me into the right direction to disable the change text box function while the initial populate happens. Hope this makes sense? many thanks. Tony

After more investigation and trial & error I have almost got it. I have a userform that appears if you double click on a row. It populates fine. If you search with "productdescription" text box (wide one at top of form) it brings back the correct rows into the list box. However in doing that the ListBox1_Click() is invoked and it thinks you selected row 1 so then refreshes the form with the data corresponding to that row.

My question is how to populate the listbox without having it auto select the first column. I am sure I am being dumb but I have been working on it all day and just cant see the problem. Please may I ask you took have a quick look. The code is attached. Just double click on a row to see the behaviour.

Many thanks

Tony