AutoComplete Feature in Drop Down Lists?

[EricC]

Hi,

Thanks in advance to any who can help me on this.

I have spreadsheet with 3000 items which I am using as a reference for data
validation. I have created a drop down list that refers to this and it is
working perfectly. The problem is that I would like to have an AutoComplete
feature for this drop down list so that I don't have to scroll through 3,000
items to select the right one. I would like to start typing a few letters and
have the reduced list come up and then select the right one.

I have already tried the previous advice from the Contextures website re:
the combo box editing but it does not work in Excel 2007. I already tried the
copy/paste of the VBA code to format the combo box and I still can't get it
to work.

Does anybody know how to get this to work in Excel 2007?

Thanks,

Eric

 

[Shane Devenshire]

Hi,

Here is one solution
http://www.ozgrid.com/Excel/autocomplete-validation.htm
and another
http://www.contextures.com/xlDataVal10.html

 

[EricC]

Hi Shane,

Thanks for your reply. Can you confirm that the Contextures workaround works
in Excel 2007? I have tried pasting the exact VBA code as described for the
combo box and cannot get Formatting/AutoComplete features to work with the
drop down menus.

Thanks,

Eric

 

[Gord Dibben]

Eric

I have an idea.

Downloaded the sample workbook from Debra's site to confirm it will or won't
work in 2007.


Gord Dibben MS Excel MVP

 

[EricC]

Hi Gord,

Thank you for replying. I downloaded the spreadsheet and it does not work.
The drop down menus are not adopting the combo box formatting written in the
code.

Eric

 

[Gord Dibben]

Do you have macros enabled?

Have you changed the filetype to *.xlsm?

Are Application Events enabled?

Perhaps someone else with 2007(I don't have 2007 installed) can test the
workbook?


Gord

 

[Stephane Guerin]

I confirm that the Contexture Autocomplete Combo sample works with Excel 2007.

Cheers!



Posted as a reply to:

AutoComplete Feature in Drop Down Lists?

Hi

Thanks in advance to any who can help me on this

I have spreadsheet with 3000 items which I am using as a reference for data
validation. I have created a drop down list that refers to this and it is
working perfectly. The problem is that I would like to have an AutoComplete
feature for this drop down list so that I don't have to scroll through 3,000
items to select the right one. I would like to start typing a few letters and
have the reduced list come up and then select the right one

I have already tried the previous advice from the Contextures website re:
the combo box editing but it does not work in Excel 2007. I already tried the
copy/paste of the VBA code to format the combo box and I still can't get it
to work

Does anybody know how to get this to work in Excel 2007

Thanks

Eric

EggHeadCafe - Software Developer Portal of Choice
WCF Workflow Services Using External Data Exchange
http://www.eggheadcafe.com/tutorial...a-6dafb17b6d74/wcf-workflow-services-usi.aspx

 

[L. Howard Kittle]

I don't know if this works in 2007, I don't have that version. But try this
'poor man's auto complete'.

Sort your validation list and at the beginning of the A's enter a single A,
do the same for the beginning of the B's and all the way through to the Z's.

Click in the cell that has the drop down validation and type in the letter
of the list you desire... say you type in an O. DO NOT hit enter... leave
the cell in the edit mode (still selected) and now hit your down arrow.

Should take you to the O which is the top of the O list of selections.

HTH
Regards,
Howard