In-cell dropdown with first character search 2

[shawnmandel]

I have a simple in-cell drop down which reads a bunch of company names from an Excel table stored within a hidden sheet in the same workbook.

Lately, the table has grown tremendously and has become quite cumbersome for users to manual scroll and search through the company names.

I haven't a clue how to begin to build a first character search function into my drop-down so that when users click on a particular letter, the cursor automatically finds the item which appears as the value in the drop-down window?

Regards,
Shawn

 

[fumei]

A dropdown just lists items in the order of the listindex.

What do you mean by "click on a particular letter? The dropdown will show the items, in the order they are in. So how can someone click, say...."T", when the first 23 items still start with "A".

Gerry

 

[shawnmandel]

fumei, sorry for not being clear.

For example, let's say I have this particular list:

Aldo
Costco
FutureShop
Staples
Zellers

When a user clicks on 'F' on the keyboard, I would like the first company that begins with F (i.e., FutureShop) to appear as the value in the dropdown list without the user having to click on the down arrow in the in-cell dropdown and scroll thru hundreds of companies.

I hope I'm more clear. Thanks for your response.

Regards,
Shawn

 

[PHV]

Doesn't have your combo a MatchEntry property ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[shawnmandel]

Hi PHV,

I cannot see any MatchEntry property for my combobox. All I see is a Control tab which I access via Format Control. I have an Input range and cell link ranges applied.

Thanks,
Shawn

 

[fumei]

Ooops. Yes, if you are using a dropdown from the Control toolbar, you can use MatchEntryFirstLetter....doh. However, if you are using a dropdown from the Forms toolbar - you don't have MatchEntry.

So use a Control dropdown.

Gerry

 

[shawnmandel]

Gerry,

My combobox dropdown is actually from the Control toolbar. I'm assuming you're referring to MatchEntry through VBA, correct?

Thanks,
Shawn

 

[shawnmandel]

Sorry, I actually have a forms combobox. I cannot get my controls combo box to read my table as it doesn't appear to have a Controls tab for me to assign my ranges as before?

Thanks,
Shawn

 

[fumei]

Well if your combobox is from the Controls toolbar, right click it and select Properties, then change MatchEntry to MtachEntryFirstLetter.

Gerry

 

[shawnmandel]

Hi Gerry,

Okay. I understand the difference. I see the MatchEntry property now. However, how could I now populate my new controls toolbar combo? I suspect I need to accomplish this with some VBA code as opposed to the forms toolbar combo, correct?

Thanks,
Shawn

 

[PHV]

I cannot get my controls combo box to read my table
What about the ListFillRange in the properties pane ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[shawnmandel]

PVH,

Okay, I filled out the ListFillRange with my range. There must be something else, since my combo is not being populated.

Thanks,
Shawn

 

[shawnmandel]

Hi PVH,

Got it. I had to exit design mode.

Thanks so much Gerry & PH