title: Excel VBA dynamic data validaiton gives "Application-defined object" error
body: <p>I am trying to get Data Validation drop down into cell "B22" in worksheet "Data" when the workbook is opened.  I get this error</p>
  
  <pre><code>Run-time error '1004'
  Application-defined object error
  </code></pre>
  
  <p>And the application breasks at this code:</p>
  
  <pre><code>.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
  xlBetween, Formula1:=value_list
  </code></pre>
  
  <p>Here is the WorkbookOpen() Sub:</p>
  
  <pre><code>Private Sub Workbook_Open()
      Dim oCon As ADODB.Connection
      Dim oRS As ADODB.Recordset
      Dim rng As Range
  
      Set oCon = New ADODB.Connection
      oCon.ConnectionString = "Provider=SQLOLEDB;Data Source=MARS;Initial Catalog=automation;Trusted_connection=yes;"
      oCon.Open
          Set oRS = New ADODB.Recordset
          oRS.ActiveConnection = oCon
          oRS.Source = "select insurer.name from person as insurer where insurer.person_class_id = 2 order by insurer.name asc"
          oRS.CursorType = adOpenStatic
          oRS.Open
          value_list = ""
  
       Do While Not oRS.EOF
           value_list = value_list &amp; oRS(0) &amp; ","
           r = r + 1
           oRS.MoveNext
       Loop
  
       value_list = Mid(value_list, 1, Len(value_list) - 1)
  
       With Sheets("Data").Range("B22").Validation
          .Delete
          .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
          xlBetween, Formula1:=value_list
          .IgnoreBlank = True
          .InCellDropdown = True
          .InputTitle = ""
          .ErrorTitle = ""
          .InputMessage = ""
          .ErrorMessage = ""
          .ShowInput = True
          .ShowError = True
      End With
  
      oRS.Close
      oCon.Close
  End Sub
  </code></pre>