Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 9th 07, 09:43 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
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,


  #2   Report Post  
Old August 9th 07, 10:15 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
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   Report Post  
Old August 9th 07, 10:30 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
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   Report Post  
Old August 9th 07, 11:08 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
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   Report Post  
Old August 9th 07, 11:23 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
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 -









Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 06:27 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017