Excel - Data validation list limit

Asked By Bart on 21-Sep-08 12:52 PM
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
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 replied on 21-Sep-08 02:42 PM
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 __
Bart replied on 22-Sep-08 02:22 PM
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 replied on 22-Sep-08 11:56 PM
hi, Bart !


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.
Anwer Yasin replied to Héctor Miguel on 20-Mar-11 11:08 PM
Please try the follow code in Excel2003 and Excel2007 then you can findout the following issue.



It runs without error. It generates only 37 items in Sheet1!A1 in Excel2003. Validation.Add does not throw an exception!



If you change the for loop size to 200 or anly number larger than 129. Validation.Add will throw an exception #1004.



Just for your information.



Sub SetListBox()

Dim R As Range

Dim i As Integer, nNumOfItems As Integer, nLenOfString As Integer

Dim S As String, stmp As String



Set R = Worksheets("Sheet1").Cells(1, 1)

On Error GoTo ErrorExit

S = "12345678"

For i = 1 To 129

S = S & Format(i, "000") & "aap,"

nNumOfItems = i

stmp = StrConv(S, vbFromUnicode)

nLenOfString = LenB(stmp)

With R.Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=S

.InCellDropdown = True

End With

Debug.Print "ERR: " & CStr(Err.Number) & " Length: " & CStr(nLenOfString) & " Number: " & CStr(nNumOfItems)

Next



ErrorExit:

If Err.Number <> 0 Then

Debug.Print Err.Description

Debug.Print "ERR: " & CStr(Err.Number) & " Length: " & CStr(m) & " Number: " & CStr(n)

End If

On Error GoTo 0

End Sub
Anwer Yasin replied to Anwer Yasin on 20-Mar-11 11:31 PM
Try following codes. It can set a listbox without any problem.



Sub SetListBox_with_Name()

Dim R As Range

Dim i As Integer, nNum As Integer



On Error GoTo ErrorExit



nNum = 500

For i = 1 To nNum

Worksheets("Sheet1").Cells(i, 2) = Format(i, "000") & "aap"

Next



ActiveWorkbook.Names.Add Name:="MyList", RefersToR1C1:="=Sheet1!R1C2:R" & CStr(nNum) & "C2"



Set R = Worksheets("Sheet1").Cells(1, 1)

With R.Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=indirect(""Sheet1!MyList"")"

End With



ErrorExit:

If Err.Number <> 0 Then

Debug.Print Err.Description

End If

On Error GoTo 0

End Sub