Data validation and Auto-complete help on Microsoft Excel

[ashwin2012]

I have 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. First I have a chart of accounts list. Second I want to enter text that is likely to be in this list, and want auto-complete to save keystroke effort by scrolling to that place in the list, or at least auto-filling, so i can then just hit enter.

[AnitG]

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 an d 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. '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!!!

[Panop]

Although data validation doesn't support auto-complete, there's a sample file here that provides a combo-box from which you can select one of the values from the data validation list. In the combo-box, you can enable auto-complete.