Workaround for Data Validation (List) limitation

**abousetta** · 04-04-2013, 09:17 AM

Hi,

I programatically add List Data Validation to a range via vba. Works fine until you close and open the workbook. An error appears stating that there are unreadable areas in the workbook and whether I would like to attempt to still read the workbook. When I say yes, then it informs that it removed the data validations.

When I put the list in another range and reference it, no problems.

The list is 50 items and growing...

Any thoughts on a workaround so that I can store the full list in vba instead of in a range on a worksheet.

Thanks.

abousetta

**JosephP** · 04-04-2013, 10:40 AM

how long is the list (# of characters)?

**abousetta** · 04-04-2013, 01:51 PM

1310 characters... too long?

abousetta

**JosephP** · 04-04-2013, 03:14 PM

much! 255 is the legal limit http://support.microsoft.com/kb/299490

**abousetta** · 04-04-2013, 03:37 PM

OK... thanks... I guess I will have to leave them in the Excel sheet and reference the range because that oddly seems to work.

abousetta

**JosephP** · 04-04-2013, 04:05 PM

a range reference is likely to be less than 255 characters so it works (similar to the issue with assigning literal values to a chart series formula versus using a range)

**abousetta** · 04-04-2013, 04:27 PM

Hi JP,

A workbook is worth a million words... or bytes... or something...

Two subs. First runs validation with the full list hard-coded into the vba code. The second runs validation with a list entered as a range from Excel.

If you run the first sub you shouldn't get any errors and validation is added to Range("A1"). Save the workbook and reopen... error... when you attempt to recover the workbook, Excel should delete the validation.

Now run the second sub which gets the list from a range. Same as before, no errors and validation is now available in Range("B1"). Save the workbook and reopen... no errors...

abousetta