Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
drop down list/typing item from list error | Excel Worksheet Functions | |||
pre-fill cell by typing first few letters of a list | Excel Worksheet Functions | |||
find item in drop down cell by typing the first few characters | Excel Discussion (Misc queries) | |||
Make typing "jump" to matching item(s) in drop-down list? | Excel Discussion (Misc queries) | |||
Speed Search in pick list by typing only first few letters | Excel Discussion (Misc queries) |