Subverting autocomplete

[Terry von Gease]

Is there any way to subvert autocomplete so that it does it's looking up in
a range other than the active column?

The effect I'm trying to achieve is have a list of names in one place, list
A, and have the user entering names in another, list B, but have the auto
complete use list A for doing the autocomplete and not list B.

I realize with some really ugly and massive use of OnKey and AutoComplete I
can give the illusion of this but that's unacceptable. I want to subvert
Excel's built in feature.

--
Terry

"I said I never had much use for one,
I never said I didn't know how to use one."
M. Quigley

 

[Anders S]

Also asked in .programming.

Please don't multipost.

Regards
Anders Silvén

 

[Dave Peterson]

I don't think so--unless you put that list (or a copy of that list) in the same
column. You could hide those rows to get them out of the way.

Have you thought about using data|validation--not quite the same as
autocomplete, but it might make data entry easier.

Debra Dalgleish has lots of notes at:
http://www.contextures.com/xlDataVal01.html

 

[immanuel]

If you've got your list A in A1:A10, you can use:

Application.AddCustomList ListArray:=Range("A1:A10")

You could use the above in a workbook open event handler. Store the custom
list count first with Application.CustomListCount, add the custom list, and
check to see if the list count changed. If it did, you could use a workbook
close event handler to remove the list with

Application.DeleteCustomList ListNum:= Application.CustomListCount

before closing the workbook so that you leave the users' application
settings in the same state as they were when they opened your file...

/i.

 

[Immanuel]

On reading the OP again, I don't see it as satisfying the original request.

Thanks for the correction,
Immanuel