validation list length issue

[svb]

When I select an item in say cell A:1, based on my selection a list is
presented in cell B:1 in the form of a validation list where the list values
are dynamically retieved from a database. This code has worked for ages,
however some of the lists are now fairly large.
I now receive the error,
---------------------------------------------------
Run-time error '-2147417848(80010108)':

Automation error
The object invoked has disconnected from its clients

---------------------------------------------------

If I reduce the number of items in the list then it works. So the question
is is there a limit on the number of items I can have in a validation list. I
don't want to use combo box objects so if there is a limit then suggestions
for a better way of doing this would be appreciated. Code snippet below.

'//strList is a comma-delimitted list of values retrieved from a db. May
have up to 200 values.
strList = "1,2,3,4,5,6,7,8,9,10,11,12,13,14,15"
'Populate list with list items from db
With curValueCell.Validation
..Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ '//debugger
selects this line as in error
Operator:=xlBetween, Formula1:=strList '//debugger selects this line as in
error
..IgnoreBlank = True
..InCellDropdown = True
..ShowInput = False
..ShowError = False

End With

ps. If above variable 'strList' contains more than 65 items the above error
occurs. It also doesn't work if I manually add a 65 item comma-delimitted
list directly into the source box via data->validation menu.

 

[Alok]

If the list actually consists of consecutive integers, you may just like to
find the minimum and the maximum values in the list and set those in data
validation. You will of course not get a drop down but except for that the
validation will still work.

 

[Bernie Deitrick]

You can add the list of items to a range, which can number over 1000 items, at least. Let's assume
that column AA is currently blank:

Dim myArr As Variant
myArr = Split(strList, ",")

Range("AA1").Resize(UBound(myArr) + 1).Value = Application.Transpose(myArr)

''Populate list with list items from db
With curValueCell.Validation
..Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=" & Range("AA1").Resize(UBound(myArr) + 1).Address
..IgnoreBlank = True
..InCellDropdown = True
..ShowInput = False
..ShowError = False

End With


HTH,
Bernie
MS Excel MVP

 

[svb]

Thanks Bernie,
Range is the way to go.

You can add the list of items to a range, which can number over 1000 items, at least. Let's assume
that column AA is currently blank:

Dim myArr As Variant
myArr = Split(strList, ",")

Range("AA1").Resize(UBound(myArr) + 1).Value = Application.Transpose(myArr)

''Populate list with list items from db
With curValueCell.Validation
..Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=" & Range("AA1").Resize(UBound(myArr) + 1).Address
..IgnoreBlank = True
..InCellDropdown = True
..ShowInput = False
..ShowError = False

End With


HTH,
Bernie
MS Excel MVP