Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populating a data validation list based on criteria | Excel Discussion (Misc queries) | |||
Validation list populating multiple cells | Excel Discussion (Misc queries) | |||
drop down list populating | Excel Discussion (Misc queries) | |||
.AddItem list and populating combobox with created list | Excel Discussion (Misc queries) | |||
Populating a Data Validation List | Excel Discussion (Misc queries) |