Data validation list limit

[BartM]

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

 

[Héctor Miguel]

hi, Bart !

AFAIK there is a constraint of ~255 characters (commas included) for a list-entry in DV ref-edit box (even by code)
your code does not fail, but any characters exceeding that limit, they are NOT (really) being assigned to "the list"

also, DV lists assigned to ranges, supports ~32,767 elements/cells/...

hth,
hector.

__ OP __

 

[BartM]

Hi Hector,

thanks for your help here. I was not "really" aware of the 255 char limit
(although one clearly hits it when setting up a validation list through the
normal Excel interface as I found out). I am not sure I understand what you
mean by "not really being assigned". In my VBA code I assign a rather long
(>>255) validaton list. By handling "SheetChange" events I catch the user
making a selection and I/the user can use every element of this large list,
exactly like I intended. It is only when I save the workbook and reopen it
that Excel complains.

Any thoughts?

Best,

Bart

 

[Héctor Miguel]

hi, Bart !

... I was not "really" aware of the 255 char limit
(although one clearly hits it when setting up a validation list through the normal Excel interface as I found out).
I am not sure I understand what you mean by "not really being assigned".
In my VBA code I assign a rather long (>>255) validaton list.
By handling "SheetChange" events I catch the user making a selection
and I/the user can use every element of this large list, exactly like I intended.
It is only when I save the workbook and reopen it that Excel complains.

AFAIK, versions prior to 2003 does really complain when trying to set a >> 255 characters validation-list (even by code)
also, some versions won't let you "quit" the application :-((

hth,
hector.

 

[mapbutton]

I appreciate that this is an older post now, but I came across this problem whilst generating a VBA routine that responded to user events using Excel 2007. The premise being that the selection of certain value using one data validation list would lead to the creation of a second data validation list.

I used VBA to construct the data validation list, but got the unreadable content alert.

Further research indicated that this is an issue with the XML format used in 2007, where 255 character strings effectively corrupt the XML coding. I was able to recover the corrupt templates by editing the XML used in the workbook, but then created an On Close event to remove these derived data validations.

As a result I could use data validation lists of more than 255 characters 'between saves'.