Validation add method fail when programming Excel with VBA
 

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

Validation add method fail when programming Excel with VBA
 

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

John Sherry wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Validation add method fail when programming Excel with VBA
 

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

"Debra Dalgleish" wrote:

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

John Sherry wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Validation add method fail when programming Excel with VBA
 

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

John Sherry wrote:
Ok, then I'll find another way to achieve what I want.

"Debra Dalgleish" wrote:


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

John Sherry wrote:

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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html