validation list giving error

[vineet78]

I am using a macro to store 100 string values in validation list -strValidList
but when i close workbook and reopen this , i get error as below.
Someone told me it is because length of validation list goes beyond 255 characters.

what should i do, i need to have this validation list greater than 255 and also do you think below error is due to this?
as when i remove this macro, i dont get this error.

 

[jasonb75]

Most people only use actual validation lists for a very small number of options, for anything else common practice is to create the list in a worksheet table (sheet hidden if desired) and refer to that as the list source.

 

[vineet78]

ya thanks Jason, i am now trying by that method only.

 

[My Aswer Is This]

Here is a example of how I use a Table as my source for a Data Validation List:
Assuming your table is named Table1

=INDIRECT("Table1")

And as you add more values to the Table the Data Validation list also expands
This assumes your Table is a one column Table

 

[Fluff]

There's no need to use indirect, you can just use the Cell range =$A$2:$A$16 & as long as you originally use the entire table range (less the header) it will expand automatically as data is added.

 

[My Aswer Is This]

There's no need to use indirect, you can just use the Cell range =$A$2:$A$16 & as long as you originally use the entire table range (less the header) it will expand automatically as data is added.

With my solution you can use the same source on any sheet in the workbook without having to use sheet names. So you can have 5 data validation lists on 5 different sheets that refer to the same Table

 

[Fluff]

You can easily do that without using a volatile function, just by adding the sheet name to the range.

=Lists!$A$2:$A$16

 

[My Aswer Is This]

You can easily do that without using a volatile function, just by adding the sheet name to the range.

=Lists!$A$2:$A$16

I see no reason why my solution is not a good one. I realize in Excel there are 10 ways or more to do anything

 

[Fluff]

I never said it wasn't good, I was just pointing out an alternative.

 

[jasonb75]

I see no reason why my solution is not a good one.

In my opinion, unnecessary use of a volatile function is an answer, not a solution.