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