I use VBA to impose data validation to a certain cell. Below is a simple
example. This works fine and a drop down list is displayed as expected. When
I save the workbook and open it again Excel complains with the well known
"...unreadable content...." message. There seems to be a limit to length of
the validation list that is not applied when imposing the data validation
list by VBA, but is applied when Excel (re)opens the book. Anyone any
suggestions, ideas? I am currently deleting all validation lists when the
workbook is closed but that is hardly an ideal situation.

Private Sub SetValidationList(ByVal R As Range, ByVal List As String)
R.Validation.Delete

With R.Validation
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:=List
.InCellDropdown = True
End With
End Sub

Private Sub Workbook_Open()
Dim R As Range
Dim i As Integer
Dim S As String

Set R = ThisWorkbook.Sheets("Sheet1").Cells(1, 1)
S = ""

For i = 1 To 256
S = S + "aap,"
Next

Call SetValidationList(R, S)
End Sub