Create List of results from a input search, select desired result, paste into worksheet

Hi guys, great forum!
I've been using excel for many years but relatively new to VBA.
I have attached a simplified version of my invoicing book which needs some fine tuning.

a) What it does at the moment:
- Sheet 1 (Invoice) - user either enters the part numbers directly into cells C7:C14 to populate corresponding cells D7 to E14, or clicks the search command button on the left which brings up an Input to enter keyword search. The keyword searches via multiple partial keywords separated by spaces (many thanks to whomever created the script!).
- Sheet 2 (ProductList) - obviously contains the data we are searching through.

b) What I would like:
- At the moment the VB script highlights the cells anywhere in the workbook that contain search results (it pages through each subsequent worksheet)
- a blank "" search also produces some result (which ain't good)
- I would like the search result to be populate a popup list (which updates real time if possible as you add keywords into the Input Box)
- Then, I would like to be able to select one of the results amongst the list of results, and have this selection transpose to the appropriate line in Invoice Sheet (Part No, Description, Price).
- I don't need the cells to be highlighted (I've left this in the VBA for now so you can easily view the script that produces results)

Any help would be greatly appreciated, thanks in advance for your time and thoughts.
Eddie

So far I've only been able to add a form/listbox and have it pop up after my query ...
I have had no luck getting it to populate. confused about code required, and where to place it (forms or module).
attached is my progress (if that's what you'd call it!), any ideas out there? InvoiceProducts2.xlsm

Hi Xlbiznes,
Thank you for your help. This is a huge step forward and quite functional. I also see how wrong my approach was and now learning from your code and where you've placed it compared to my initial effort.

Can I request some changes if you are able to make them?
I noticed that the part numbers have to start with '0' - although on my example sheet, the data does have part numbers that start with 0, this is just sample data that I pasted from the net just to create something. In practice, part numbers might start with any number or letter (e.g. 03-443232, or PTS9903, SS-4324-44 etc...) – Are you able to amend the code to accommodate this ? I see how in your code you've include '0' to ensure column 'A' is searched first. If not possible, perhaps two text boxes used to search (one for part number search, one for description)?

Regarding the search functionality, the original search was able to search within a cell for partial keywords, in any order and quantity of keyword, and it excluded results with the addition of keywords (e.g. entering a search "sel lab rol" - would still bring up a product description that contains: "self adhesive roll labels", even though the order is wrong).
The current search input has to sequentially match the target cell (e.g. entering the above search would not yield a result, but you would have to search "sel*rol*lab" in sequence, with * between keywords, to get the same result).
I really appreciate your help thus far, it's huge!
Many thanks,
Eddie

Hi, yes, ok if it searches both columns. Regarding the order of the keywords, it would be ideal if it could search in any order, sel rol lab, rol lab sel, etc... and still call the same result, like on the first workbook. This is because the product sheet will potentially hold many thousands of entries. Unfortunately products do not follow a specific protocol for the description (e.g. product1: "table, dinner, timber, stained, 6 seater", product2: "dinner table, timber, 6 seater, black" - need to find both using keyword "dinner, table". What do you think? Are you able to incorporate the search code from the first wbk? Thanks again in advance!!

Hi Xlbiznes,
Thank you very much, I greatly appreciate your help.
The A:B works well. The * before the search doesn't create the search but to be honest, I think I am asking to much to have both multiple keywords search in any sequence producing real time drop-down list. Nonetheless, what you have provided is great and it will do fine
Oh, one more thing, if you get a chance, if we were to expand to include searching in column C, would this be:

Set Rng = wks.Columns("A:C").Find(What:=Me.TextBox1.Value, After:=wks.Range("A" & wks.Rows.Count).End(xlUp))

or require an additional loop?

P.S. Hope you have a great 2014.
Cheers,
Eddie

Hi again,
I changed the code ("A:C") as above, also included a category in product list (column B of product list) -- I have found that when you search now, if the name of the category is the same text in the description, then your search will not include other items in the category, it stops at the first row found that matches.

I have attached the modified sheet (only changed B to C, and included category) Updated InvoiceProducts - Xlbiznes-3.xlsm.
e.g. try to search "GLE", this will bring up only one line, and not the other items that include GLE in either description or category.

Cheers, Eddie

That's great!!! works very well. Thank you again!

Nice, I was looking for the change to your code: And ctr > 1.