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" wrote in message
...
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.