Navigate to an item in pulldown by typing first couple of letters

  • Thread starter Thread starter ji.h.lee
  • Start date Start date
J

ji.h.lee

I have a list in sheet A and on sheet B I have a cell with a pull down
menu that lists the list from the sheet A. The list contains 2400+
items, and instead of scrolling down to the specific item, I am
looking for a way to type the first couple of letters to get near the
item I need to get. Is there anyway to get this done? Sheet B
appears to be somewhat restricted such that recording or writing VBA
is prohibited. Are there any functions or properties I can use to
accomplish this?

Thanks,
 
You can use a combo box from the control toolbox. It has a MatchEntry
property that will do what you want.
 
Is there a way to use matchentry with the pulldown menu under the
validation function?

Also I am not sure how to use the match entry functionality in the
combobox context. how do i type stuff into the combo box?
 
Is there a way to use matchentry with the pulldown menu under the
validation function?
Click to expand...

Not directly. There is a kludge work around:

Sort the source for the dropdown and add the first letter to the beginning
of each group. Like this:

A
Adam
Allen
Arron
B
Becky
Bill
Bob
C
Carla
Chris

Then when you want to select the Cs, type a C in the cell then click the
drop arrow and it will take you the Cs.
Also I am not sure how to use the match entry functionality in the
combobox context. how do i type stuff into the combo box?
Click to expand...

A combo box is very similar to a data validation drop down list. There are 2
types of combo boxes, a Forms combo box and a Control toolbox combo box. The
Forms combo box will not do what you want (without some VBA coding).

To setup a Control toolbox combo box:

Right click any toolbar
Select Control toolbox
Click the first icon on the top left (it has a blue "thing" and what looks
like a pencil tip) (aren't icons wonderful!)
This will put you in design mode. After you click that icon and hover your
mouse over it, it should say Exit design Mode.
Now, click on the combo box icon then goto the location where you want it to
appear
Click and hold then draw the combo box to the shape and size you want it.
Then, right click and select Properties
Scroll down and select ListFillRange. This is the same thing as the source
for the data validation drop down list. Click in the box on the right and
enter the source for the list. For example, Sheet1!$A$1:$A$100. Then hit
Enter
Now, scroll down and select MatchEntry
From the drop down on the right select either 0-fmMatchFirstLetter or
1-fmMatchEntryComplete
Close the Properties dialog
Exit design mode by clicking the first icon on the top left.

Your combo box is now ready for use.
If you ever need to edit the properties you have to open the control toolbox
toolbar and go back into design mode.
Sounds somewhat involved but once you get to used to it there's nothing to
it.
 
Tweak:
Then when you want to select the Cs, type a C in the cell then click the
drop arrow and it will take you the Cs.
Click to expand...

Then when you want to select the Cs, type a C in the cell but *DON'T* hit
the Enter key, click the
drop arrow and it will take you the Cs.
 
Back
Top