AutoComplete Feature in Drop Down Lists?

EricC

unread,

Jan 5, 2009, 1:55:01 PM1/5/09

to

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

unread,

Jan 5, 2009, 2:39:01 PM1/5/09

to

Hi,

Here is one solution
http://www.ozgrid.com/Excel/autocomplete-validation.htm
and another
http://www.contextures.com/xlDataVal10.html
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

EricC

unread,

Jan 5, 2009, 3:28:01 PM1/5/09

to

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

unread,

Jan 5, 2009, 7:02:00 PM1/5/09

to

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

On Mon, 5 Jan 2009 12:28:01 -0800, EricC <Er...@discussions.microsoft.com>
wrote:

EricC

unread,

Jan 6, 2009, 4:45:01 PM1/6/09

to

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

unread,

Jan 7, 2009, 1:53:40 PM1/7/09

to

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

On Tue, 6 Jan 2009 13:45:01 -0800, EricC <Er...@discussions.microsoft.com>

stephaneguerin

unread,

Sep 19, 2009, 12:39:15 PM9/19/09