Validation add method fail when programming Excel with VBA

[Guest]

Has anybody else had the following problem?
I have a Validation object on a Target cell, I change the validation list
dynamically in an event handler (first deleting the original list) with the
add method and a delimited List in a String.

Now if the list is longish (say 256 characters total, 26 surnames) I get an
add method failure message and Excel exits. With shorter lists it doesn't
happen.

Now dynamic Strings should be good to about 2G characters, I can debug.print
the string okay.

Is there a limit on the number of items or characters that can appear in the
Validation List?

I want to avoid using a range of cells to hold the list.

I have tried this on Win2K with Office 2000, and on WinXP with Office 2003
same failure each time.

Thanks in advance

 

[Debra Dalgleish]

There's a 255 character limit on the delimited list for Data Validation.

 

[Guest]

Ok, then I'll find another way to achieve what I want.

 

[Debra Dalgleish]

You didn't say why you want to avoid using a range, but perhaps you
could use a named list on a hidden worksheet.