How many entries can you have in List Data Validation

[Rick De Marco]

Hi,

Does anyone know what the maximum number of entries you can have in a
data validation list? And, is there a limit of characters for each
entry?

Thanks,

Rick

 

[Mike]

Data validation, and other lists (ie filters) can have up
to 1000 unique entries, but only 8 will fit in the list at
a time, which makes for a lot of scrolling.

I don't know if there is a limit to the number of
characters per entry, but you'd probably want to keep it
down to less than the width of the cell you are using.
For a test, I used a list with 300 character fields, but
that's too wide to fit in the entire screen width let
alone a single cell.

 

[Debra Dalgleish]

If you are typing the list in the data validation dialog box, you're
limited to 255 characters, including the commas that separate the list
items.

If you refer to a list on the worksheet (e.g. =MyList), the limit seems
to be 32767 items.

The list will display the first 255 characters of each item, but is
limited to the width of the column. (For very narrow columns, it will
extend to about 1/2".)

 

[Rick De Marco]

Hi Debra,

Thanks heaps for replying to my question? When mention refering to a
list on the worksheet (e.g. =MyList), the limit seems to be 32767 items.
How do your accomplish this in vba? Currently I am just using something
like

Cells(10,10).Validation.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="my list"

How do I get 32767 chars in my list? Do you have any other ideas how I
might solve this problem??

Thanks again,

Rick


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Debra Dalgleish]

You could enter the list in a column on a worksheet, e.g. Sheet2!A1:A32767

Then, select those cells, and click in the Name box, at the left of the
formula bar. Type a one word name, e.g. MyList, and press Enter.

You can refer to this list in the code: Formula1:="=MyList"

 

[Rick De Marco]

Hi Debra,

Thanks heaps for replying to my question? When mention refering to a
list on the worksheet (e.g. =MyList), the limit seems to be 32767 items.
How do your accomplish this in vba? Currently I am just using something
like

Cells(10,10).Validation.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="my list"

How do I get 32767 chars in my list? Do you have any other ideas how I
might solve this problem??

Thanks again,

Rick


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!