title: VBA Excel adding a dynamic validation list
body: <p>I have a worksheet in which I am trying to programmatically add DataValidation-Lists when appropriate to the <code>Range(BF:BZ)</code></p>
  
  <p>Currently all cells in <code>Range(D3:D500)</code> use a DataValidation list <code>=type</code> and I have a <code>sub</code> that checks if any cells in that range change. If so it updates all the cells in that row. When I get to the <strong>options</strong> range it references a table on a separate sheet for what to fill in.</p>
  
  <p><img src="https://i.stack.imgur.com/76OAC.png" alt="example of different types"></p>
  
  <p>At this point when filling each <strong>option</strong> I need to also create a DataValidation-list when appropriate, based on the text in the cell. row 6 is an example of results that would require a list, while rows 3 through 5 have multiple results that should just be left as is (for someone to manually type in an ID later or something along those lines)</p>
  
  <pre><code>Dim formString As String
  Let formString = "=" &amp; Range(colAddress)
  Range(colAddress).Select
      With Selection.Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
      xlBetween, Formula1:=formString
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = "InputTitle"
      .ErrorTitle = "ErrorTitle"
      .InputMessage = "InputMsg"
      .ErrorMessage = "ErrorMsg"
      .ShowInput = True
      .ShowError = True
  End With
  </code></pre>
  
  <p>This code works in the case that there is a valid DataValidation-list, but I get a <code>Run-time error '1004': Application-defined or object-defined error</code> when <code>formString</code> has values such as <code>speed_OW</code></p>
  
  <p>Would I have to make essentially a list of lists which contains all the valid lookups and call something like <code>IFERROR(VLOOKUP(Range(colAddress),listOfLists,1,FALSE),"No List")</code></p>
  
  <p>Or is there a cleaner way of doing this so as to:  if a data-validation-list exists for the current cell then set it, else leave the cell alone for the user to manually change</p>
  
  <p><strong>Edit for additional information below</strong></p>
  
  <p>So in another question someone mentioned making references to the <em>Name Manager</em> and I think just looking through that is all I need to check in a conditional. I will try this tomorrow</p>