Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
validation list length issue
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
validation list length issue
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. "svb" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
validation list length issue
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
validation list length issue
Thanks Bernie,
Range is the way to go. "Bernie Deitrick" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation List Length | Excel Worksheet Functions | |||
Data Validation and List Length | Excel Programming | |||
Validation (Drop down list vs simple text length validation) | Excel Programming | |||
Validation (Drop down list vs simple text length validation) | Excel Programming | |||
Validation (Drop down list vs simple text length validation) | Excel Programming |