title: Data validation with vba
body: <p>Normally it is possible to apply data validation in excel through vba but i am having a specific problem</p>
  
  <p>I am using the indirect method for applying validation,normally when i dont use vba i get a warning by excel that "source currently evaluates to an error,do u want to continue?" (this is because cells which indirect refers to may be empty), now i can easily skip this error in excel by clicking "Yes" </p>
  
  <p>Here's the link <a href="http://www.contextures.com/xldataval02.html" rel="nofollow">http://www.contextures.com/xldataval02.html</a> (Dependent validation lists)</p>
  
  <p>But when i try to perform the same thing using vba i am getting a runtime error,there is no friendly prompt which allows me to continue.How can i handle this kind of error.</p>
  
  <p>On error resume isnt working because then vba doesnt apply validation at all.</p>
  
  <p>Here's the code</p>
  
  <pre><code>Set rng = ThisWorkbook.Sheets("input").Range("AB11:AB65536")
  With rng.Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
      xlBetween, Formula1:= _
      "=INDIRECT(cablecode&amp;""_depth_""&amp;$Q11&amp;""_""&amp;$Z11&amp;""_values"")"
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
  End With
  </code></pre>
  
  <p>cablecode is a static named range </p>
  
  <p>Q11 and Z11 refer to inputs in that particular row</p>
  
  <p><em>depth</em> and <em>values</em> are normal strings</p>
  
  <p>cablecode can have two values = "is" or "iec"</p>
  
  <p>Q11 can be "xlpe" or "pvc"</p>
  
  <p>Z11 can be "al" or "cu"</p>
  
  <p>since cablecode is constant for whole project ive referred to it directly,
  Q11 and Z11 can be different for different rows so ive referred to them seperately</p>
  
  <p>The whole string comes out to be
  "is_depth_al_xlpe_values"
  similarly different permuations,and all named ranges are already defined</p>