Need help to create list/combo box search macro

[tryexcelmacro]

Hi Guys,

I've attached sample with the type of result needed. I'm creating a userform with some search feature and i'm not planning to use sourcedata for the result box. I need help to create below requirement since i can only find checkbox type of search. Can anyone help me out on this?

1. Search box contain the text to enter and result box will show as you type.
2. The type box is for selecting from a list/combo box for fruits/vegetable etc.

[kaasplankje]

Some further hints would be appreciated.

I assume that in this example, the type box shows either fruit or vegetable, right?
Then, what should show in search box? Does it depend on what is selected in the type box? Please give an example.
Then, what should show in the results box? Does it depend on what is selected in the type box and the search box? Please give an example.

[tryexcelmacro]

Hi,

Yes. Type box will be either list/combo box that has a drop down of selection from row 1 (header). In this case, it'll be fruit & vegetable. I'm actually thinking of naming this row as a header inside name manager but not sure it will make the coding more messy. Maybe just selecting those 2 cells would be easier?

The result box should show couple of column data. Maybe just 2 or 3 selected column. Since this example only has limited column, showing all 5 wouldn't be a problem if the box is big enough.

The worksheet(result) column A to D would be a sample of what will be shown in result. Row 1-5 would be the result when i select fruits in type combo/list box and search the text apple. While row 8-10 would be result when vegetable was selected in the type while searching for Zucchini.

[torachan]

for ease of data handling table layout needs addressing - try the attached - where do you want your result to display ?

[tryexcelmacro]

Hi Torachan,

Thanks for the help. It's very similar to what i'm planning to have except below few points.

1. I would like the result to display in the listbox in your example.
2. In you example, the combo box 2 dropdown box isn't that important as a normal text box for search would be sufficient. It works well to me too.
3. I notice that you group the vegetable & fruit in a single column. Is it possible to search them in 2 different column instead? I'm looking for something similar to youtube video (youtube.com/watch?v=iHTKk0MVQxA OR youtube.com/watch?v=9PuVRE7ARKA) but in userform.

[torachan]

1)The listbox acts as your third filter to obtain the one unique row. (if you had an unique identifier you could do this with a third combobox).
2) comboboxes are used because you can be sure the data been searched is actually in the database (with a textbox you introduce the element of time wasting guesswork)
3) the layout is as you would construct in 'pseudo' database - (they are only grouped because I merely copy/pasted them - jumble them up you will see it works just the same)
The last thing you want to do is need to alter the structure of your database (i.e. have to add extra columns for added groups ('meat' , 'dairy' etc.,)
The code technique used in the tutorial is 'clunky' and old - use the power of 'structured tables' and their handling techniques and arrays to give you speed without constantly reading the sheet.
the attached updated file takes your selection from the listbox to the form.

[tryexcelmacro]

hi torachan,

I think the data you're using is different from my original sample.

I've update partial macro with added userform for my file below. I need help to search using the textbox which show result as i type in the listbox form. textbox for this sample will search for only text but i might be using it to search both number & text.

[beyond Excel]

Hello. See if it's the powerful RowSource you're looking for:

[tryexcelmacro]

i'm actually looking for a simple one with updated file in post 7.

[torachan]

you do not give any indication how large your data storage is likely to be - however the larger it becomes the greater the nightmare will be with a format as post#7.

[beyond Excel]

i'm actually looking for a simple one with updated file in post 7.

I understand: what you want is not simpler but more complicated... But here's the adjustment!

[tryexcelmacro]

Thanks for the help. Manage to found something similar online. Will add that and what you guys suggested to my macro.