title: How to get excel drop down list source in vba
body: <p>Im using <a href="https://stackoverflow.com/questions/64261013/multiple-selection-dropdown-list-in-excel">VBA code to create multiple selection drop down list</a>. The code will make each drop down list in target cell become multuple selection list with function:</p>
  <p><code>If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub</code></p>
  <p>The source of drop down list in target cell is <code>=indirect(b14)</code>, and b14 is another drop down list (single selection). Now if b14's value will become list1, Id like to make my target's cell list become multiple selection list. In any other case I want it work in normal excel way. I've tried to precache the list source with <code>if Evaluate(Target.Validation.Formula1) = &quot;=list1&quot; then</code>
  but I get mismatch error for <code>Evaluate(Target.Validation.Formula1)</code>.
  How can I do it?</p>
  <p>EDIT:
  There are some example screenshots from my worksheet, not to misunderstand it's construction.</p>
  <p><a href="https://i.stack.imgur.com/wfTXy.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wfTXy.jpg" alt="A1:A5 named range list1, B1:B5 named range list2, B14 data validation list =list1" /></a>
  A1:A5 named range list1, B1:B5 named range list2, B14 data validation list =list1</p>
  <p><a href="https://i.stack.imgur.com/os4Ns.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/os4Ns.jpg" alt="D14 data validation list with =INDIRECT(B14) formula" /></a>
  D14 data validation list with =INDIRECT(B14) formula</p>