data validation and autocomplete -- again...
 

hi,

i've looked at the discussions about this topic, and i can't help but feel
someone must have created a better solution. does anyone know of a better
solution?

the problem is simple:
1. i have a chart of accounts list
2. i want to enter text that is likely to be in this list, and want
autocomplete to save keystroke effort by scrolling to that place in the
list, or at least autofilling, so i can then just hit enter.

thanks for any help here,

fred

thanks max, this is very helpful.

fred


"Max" wrote in message
...
Maybe this recent post by Debra Dalgleish might be of help:

Although data validation doesn't support autocomplete, there's a sample
file here that provides a combobox from which you can select one of the
values from the data validation list. In the combobox, you can enable
autocomplete:


http://www.contextures.com/excelfiles.html


Under 'Data Validation', look for 'Data Validation Combobox'

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
Fred wrote in message
...
hi,

i've looked at the discussions about this topic, and i can't help but
feel
someone must have created a better solution. does anyone know of a
better
solution?

the problem is simple:
1. i have a chart of accounts list
2. i want to enter text that is likely to be in this list, and want
autocomplete to save keystroke effort by scrolling to that place in the
list, or at least autofilling, so i can then just hit enter.

thanks for any help here,

fred

Pleasure` Fred !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Fred" wrote in message
...
thanks max, this is very helpful.

fred

I have a related question about this.

How do you return, in VBA, a value to state whether Data Validation is in effect on a specified range?

I can't find a way to programatically test whether a Validation object exists on a Range. Instead, the only way is to try and read a Validation property and see if it returns the error "Object doesn't support this property or method". This is no good for operating on a Validation property if it exists but skip that code if it doesn't.

Even using On Error Goto ... doesn't trap this error.

I've tried looking for a 'HasValidation' type of property but nothing seems to exist.

I've also tried using xlSpecialCells(xlAllDataValidation) but all this does is return all cells in the same column as the specified Range - because it treats 'no validation' as a validation - even though there is no Validation Object!!!

Maybe this recent post by Debra Dalgleish might be of help:

Although data validation doesn't support autocomplete, there's a sample
file here that provides a combobox from which you can select one of the
values from the data validation list. In the combobox, you can enable
autocomplete:


http://www.contextures.com/excelfiles.html


Under 'Data Validation', look for 'Data Validation Combobox'

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
Fred wrote in message
...
hi,

i've looked at the discussions about this topic, and i can't help but feel
someone must have created a better solution. does anyone know of a better
solution?

the problem is simple:
1. i have a chart of accounts list
2. i want to enter text that is likely to be in this list, and want
autocomplete to save keystroke effort by scrolling to that place in the
list, or at least autofilling, so i can then just hit enter.

thanks for any help here,

fred