Data validation limitation: List source (Run-time error 1004)

[n8Mills]

If you've been using Data Validation with excel, especially automating it with lists, then you know it can be a handy thing. For those of you who don't know what it is; you can turn any cell into a combo box.

But lately My attempts to automate with this feature have run into Run-time 1004 errors. I have discovered that there is a 255 character limit on the "Source" field (or "Formula1" in VBA). Here's the code I used to figure this out:

[vba]Sub test()

varStr = "Test"
i = 1
While i - 1 < 50
testStr = testStr & i & varStr & ","
With Range("A1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=testStr
.IgnoreBlank = True
.InCellDropdown = True
.IMEMode = xlIMEModeNoControl
.ShowInput = True
.ShowError = True
showStringLen = Len(.Formula1)
End With
Range("B1") = "String length = " & Len(testStr) & ", List string length = " & showStringLen
i = i + 1
Wend

End Sub[/vba]
While it may not error out until well after 255 characters, everything after 255 characters is lost.

For more entries to your Data Validation list you need to instead point to a series of contiguous cells in your source field, such as "=$B$2:$B$375". While this may not be optimal, Data validation does have this limitation.

In case you're wondering why I'm posting this, it's because my Google searches for "Data Validation Limitations" came back with people asking the same question and getting no answers.

[lucas]

Have you tried using a dynamic named range for the list source? The names can be much shorter than other formula's in the source field.

Another advantage of named ranges is the list can be on another sheet.


In the attached example see the code for sheet 2 to see how the dynamic named range is maintained and on sheet one the highlighted cell has the data validation.

[Bob Phillips]

I have to ask, unless it is a very simple list such as Yes, No, why would you ever build the list in code and not hold it in a worksheet and just refer to that. Seems like a maintenance nightmare to me.

[n8Mills]

Lucas,

Thanks for the idea. I tried it and it works manually, but when I try to set the list source from a named range on a different sheet I get a "object error". I'll have to tinker with it to see what's at fault.

xId,

We have about 20 "Work Centers" that if you are going to run a whitelist or blacklist against you can't have typos. As this list will likely not change significantly, the above code works fine to make it accessible and difficult for people to inadvertently corrupt.

[Bob Phillips]

Just put the list in a hidden sheet, then they wouldn't tinker.

[n8Mills]

I could do that, if what I'm doing starts to give me problems. For now it's fine. In fact, I'll have to do it with the customer list, being that there are 217 of them.

Hey, can you tell me why this works:
[vba]Range(Range("namedRange"), Range("namedRange").Offset(150, 0)).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=custBLcand"
End With[/vba] But this doesn't?
[vba]With Range(Range("namedRange"), Range("namedRange").Offset(150, 0)).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=custBLcand"
End With[/vba] Kinda weird. The only difference is the use of Select/Selection.

[Bob Phillips]

In what way does the latter not work? It works fine for me.

[n8Mills]

There must be something else going on... there usually is.

I'll dig into it more. Thanks for the confirmation!