| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
drop down list accessed by typing the first few letters
I am making a series of spreadsheets for our sales team. I have one that has
several drop down lists for those of them that do not like to type, however, those that do type frequently want a drop down list that will be accessible by typing the first few letters of the item, and choosing from the choices. The drop down lists that I have are made through data validation, but is there a macro that could let me do this programatically? Any help would be appreciated. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
drop down list accessed by typing the first few letters
You can use the combo box ActiveX object of the Control Toolbox toolbar to do
the "Match as you type" functionality that you want. You can create your lists in the worksheet and when you insert the combobox, right click on it and select properties. The ListFillRange property is the range in the worksheet where your list is located The LinkedCell property is the cell that you want to recieve the selected value The MatchEntry property needs to be set to 1 - fmMatchEntryComplete You'll have to add the combo boxes as needed one at a time, but you can copy an existing one and just change the ListFillRange and the LinkedCell address. It's a bit more work but it's much more functional than the data validation method. -- Kevin Backmann "Pam" wrote: I am making a series of spreadsheets for our sales team. I have one that has several drop down lists for those of them that do not like to type, however, those that do type frequently want a drop down list that will be accessible by typing the first few letters of the item, and choosing from the choices. The drop down lists that I have are made through data validation, but is there a macro that could let me do this programatically? Any help would be appreciated. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
drop down list accessed by typing the first few letters
okay, I'm lost. I'm using 2007 Excel, which I'm still having to hit help
every time I try to find something cause it's all "iconized". according to the instructions I found here http://support.microsoft.com/kb/291073 I have entered a combo box. The Index is messing me up, because I want it to index the same range? But the properties do not come up with ListFillRange and LinkCell and MatchEntry Should I be inserting a VBA form into Excel instead of what I'm doing? Sorry, It's been a long couple days and my brain is not wrapping around this. "Kevin B" wrote: You can use the combo box ActiveX object of the Control Toolbox toolbar to do the "Match as you type" functionality that you want. You can create your lists in the worksheet and when you insert the combobox, right click on it and select properties. The ListFillRange property is the range in the worksheet where your list is located The LinkedCell property is the cell that you want to recieve the selected value The MatchEntry property needs to be set to 1 - fmMatchEntryComplete You'll have to add the combo boxes as needed one at a time, but you can copy an existing one and just change the ListFillRange and the LinkedCell address. It's a bit more work but it's much more functional than the data validation method. -- Kevin Backmann "Pam" wrote: I am making a series of spreadsheets for our sales team. I have one that has several drop down lists for those of them that do not like to type, however, those that do type frequently want a drop down list that will be accessible by typing the first few letters of the item, and choosing from the choices. The drop down lists that I have are made through data validation, but is there a macro that could let me do this programatically? Any help would be appreciated. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
drop down list accessed by typing the first few letters
Ok Kevin,
I got the properties, and got the box, changed the properties and it shows my data. But how do I get it to allow me to type and do an autofill? Thanks Pam "Kevin B" wrote: You can use the combo box ActiveX object of the Control Toolbox toolbar to do the "Match as you type" functionality that you want. You can create your lists in the worksheet and when you insert the combobox, right click on it and select properties. The ListFillRange property is the range in the worksheet where your list is located The LinkedCell property is the cell that you want to recieve the selected value The MatchEntry property needs to be set to 1 - fmMatchEntryComplete You'll have to add the combo boxes as needed one at a time, but you can copy an existing one and just change the ListFillRange and the LinkedCell address. It's a bit more work but it's much more functional than the data validation method. -- Kevin Backmann "Pam" wrote: I am making a series of spreadsheets for our sales team. I have one that has several drop down lists for those of them that do not like to type, however, those that do type frequently want a drop down list that will be accessible by typing the first few letters of the item, and choosing from the choices. The drop down lists that I have are made through data validation, but is there a macro that could let me do this programatically? Any help would be appreciated. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
drop down list accessed by typing the first few letters
After much trial and error, I got this to work and it works perfectly.
THANK YOU KEVIN!!! "Kevin B" wrote: You can use the combo box ActiveX object of the Control Toolbox toolbar to do the "Match as you type" functionality that you want. You can create your lists in the worksheet and when you insert the combobox, right click on it and select properties. The ListFillRange property is the range in the worksheet where your list is located The LinkedCell property is the cell that you want to recieve the selected value The MatchEntry property needs to be set to 1 - fmMatchEntryComplete You'll have to add the combo boxes as needed one at a time, but you can copy an existing one and just change the ListFillRange and the LinkedCell address. It's a bit more work but it's much more functional than the data validation method. -- Kevin Backmann "Pam" wrote: I am making a series of spreadsheets for our sales team. I have one that has several drop down lists for those of them that do not like to type, however, those that do type frequently want a drop down list that will be accessible by typing the first few letters of the item, and choosing from the choices. The drop down lists that I have are made through data validation, but is there a macro that could let me do this programatically? Any help would be appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to show a list of words by typing a few letters | Excel Discussion (Misc queries) | |||
| SELECTING FROM A DROP DOWN LIST BY TYPING FIRST CHARACTER | New Users to Excel | |||
| 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 | |||
| Speed Search in pick list by typing only first few letters | Excel Discussion (Misc queries) | |||
Linear Mode
