n8Mills
11-29-2008, 09:32 PM
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:
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
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.
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:
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
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.