Populating a Validation List with SQL
 

I keep getting an error (Automation Error. The object invoked has disconnected from its clients.) when I'm using a variable with a lot of data to populate my validation list box using SQL. However, if I hardcode in the same values instead of using a variable then it works fine. I would like to supply this information dynamically from a database so the data is updated without user intervention. Any ideas? Here is the code I'm using

Sub Build_Dropdown_List(ByVal strWrkSheet As String, ByVal strRange As String, ByVal strValues As String
Dim strQuote As Strin
On Err GoTo errHandle

strQuote = """
With Worksheets(strWrkSheet).Range(strRange).Validatio
.Delet
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
xlBetween, Formula1:=strValues <--------------"THIS IS THE PLACE WHERE THE PROBLEM OCCURS"---------------
.IgnoreBlank = Tru
.InCellDropdown = Tru
.InputTitle = "
.ErrorTitle = "
.InputMessage = "
.ErrorMessage = "
.ShowInput = Tru
.ShowError = Tru
End Wit
End Su

Populating a Validation List with SQL
 

Rone

I don't know this for a fact, but I'll be there's a limit to what you can
pass to Formula1. And I'll be it's 255 characters, but I haven't tested it.
Try this

Operator:= _ xlBetween, Formula1:=Left(strValues,254)

If that works, keep going up and see when it breaks.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


Rone wrote:
I keep getting an error (Automation Error. The object invoked has
disconnected from its clients.) when I'm using a variable with a lot of
data to populate my validation list box using SQL. However, if I
hardcode in the same values instead of using a variable then it works
fine. I would like to supply this information dynamically from a
database so the data is updated without user intervention. Any ideas?
Here is the code I'm using:

Sub Build_Dropdown_List(ByVal strWrkSheet As String, ByVal strRange As
String, ByVal strValues As String) Dim strQuote As String
On Err GoTo errHandler

strQuote = """"
With Worksheets(strWrkSheet).Range(strRange).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _ xlBetween, Formula1:=strValues
<--------------"THIS IS THE PLACE WHERE THE PROBLEM
OCCURS"---------------- .IgnoreBlank = True .InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub