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.