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.

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.

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.

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.

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.