Populating a Validation List with SQL

[Guest]

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

 

[Dick Kusleika]

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.