A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

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



 
 
Thread Tools Display Modes
  #1  
Old August 9th 07, 09:43 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Navigate to an item in pulldown by typing first couple of letters

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,

Ads
  #2  
Old August 9th 07, 10:15 PM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,768
Default Navigate to an item in pulldown by typing first couple of letters

You can use a combo box from the control toolbox. It has a MatchEntry
property that will do what you want.

--
Biff
Microsoft Excel MVP


> wrote in message
ups.com...
>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,
>



  #3  
Old August 9th 07, 10:30 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Navigate to an item in pulldown by typing first couple of letters

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?



On Aug 9, 2:15 pm, "T. Valko" > wrote:
> You can use a combo box from the control toolbox. It has a MatchEntry
> property that will do what you want.
>
> --
> Biff
> Microsoft Excel MVP
>
> > wrote in message
>
> ups.com...
>
>
>
> >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,- Hide quoted text -

>
> - Show quoted text -



  #4  
Old August 9th 07, 11:08 PM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,768
Default Navigate to an item in pulldown by typing first couple of letters

>Is there a way to use matchentry with the pulldown menu under the
>validation function?


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?


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.

--
Biff
Microsoft Excel MVP


> wrote in message
oups.com...
> 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?
>
>
>
> On Aug 9, 2:15 pm, "T. Valko" > wrote:
>> You can use a combo box from the control toolbox. It has a MatchEntry
>> property that will do what you want.
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>> > wrote in message
>>
>> ups.com...
>>
>>
>>
>> >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,- Hide quoted text -

>>
>> - Show quoted text -

>
>



  #5  
Old August 9th 07, 11:23 PM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,768
Default Navigate to an item in pulldown by typing first couple of letters

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.


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.

--
Biff
Microsoft Excel MVP


"T. Valko" > wrote in message
...
> >Is there a way to use matchentry with the pulldown menu under the
>>validation function?

>
> 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?

>
> 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.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> > wrote in message
> oups.com...
>> 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?
>>
>>
>>
>> On Aug 9, 2:15 pm, "T. Valko" > wrote:
>>> You can use a combo box from the control toolbox. It has a MatchEntry
>>> property that will do what you want.
>>>
>>> --
>>> Biff
>>> Microsoft Excel MVP
>>>
>>> > wrote in message
>>>
>>> ups.com...
>>>
>>>
>>>
>>> >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,- Hide quoted text -
>>>
>>> - Show quoted text -

>>
>>

>
>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
drop down list/typing item from list error Strike Eagle Loader[_2_] Excel Worksheet Functions 1 May 1st 07 06:35 PM
pre-fill cell by typing first few letters of a list Trying to learn! Excel Worksheet Functions 1 March 24th 07 05:10 AM
find item in drop down cell by typing the first few characters noninon Excel Discussion (Misc queries) 1 April 17th 06 02:54 AM
Make typing "jump" to matching item(s) in drop-down list? Kathy Excel Discussion (Misc queries) 4 November 22nd 05 10:25 PM
Speed Search in pick list by typing only first few letters Shrikant Excel Discussion (Misc queries) 2 August 25th 05 01:39 PM


All times are GMT +1. The time now is 03:18 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.