title: Data Validation using named Ranges containing only strings, no addresses
body: <p>I'm creating a workbook/tracker for a group of not so comfortable excel users, hence I've named all the ranges as seen in the picture linked below (Sorry I don't have enough rep to put it in my post). Their scope is workbook.</p>
  
  <p>I've given them all a name, i.e. "TEST" &amp; "TESTMEMBERS" and have filled their content manually in the UI (<kbd>Ctrl</kbd> <kbd>F3</kbd>) so that the users may amend the named ranges easily should they need to. Their contents are simply a string instead of referring to a range of cells somewhere in the workbook. </p>
  
  <p>For example: </p>
  
  <blockquote>
    <p>"TESTMEMBERS" has the following data (aka refers to): ="Member1, Member2" </p>
  </blockquote>
  
  <p>My question is how would I go about getting the data from the named range to be used for data validation of a cell using VBA?</p>
  
  <p>I get the following error at the line at <strong>Set regOffice</strong>:</p>
  
  <blockquote>
    <p>Run-time error '1004'
    Method 'Range' of object '_Worksheet' failed</p>
  </blockquote>
  
  <p>Below is the part of my code which is having problems. It is worth noting that the sub is the <strong>Worksheet_Change</strong>.  Please let me know if you need more. I'll happily post it. I've included the DIM section to ensure I've set everything up as it should.</p>
  
  <p><pre><code></p>
  
  Dim regOffice As Range                   'Range which will contain all the regional offices
  Dim hearingLoc As Range                 'Range which will contain the hearingLoc specific to the Reg Office
  Dim wsTracker As Worksheet              'Worksheet var which will ref the Tracker Worksheet
  Dim colCounter As Long                  'Counter
  Dim i As Long
  
  
  Dim hearingDates As Range
  Dim day As Range
  Dim tableHeaders As Range
  Dim header As Range
  Dim colLoc As Long
  Dim colOpened As Long
  Dim colScheduled As Long
  Dim colRecording As Long
  Dim colAdjourned As Long
  Dim foundLocation As Range
  
  Set wsTracker = Application.ActiveSheet
  
  
              Set regOffice = Range("TEST") 'The purpose here is to use regOffice range later and iterate through it's values.
  
              'Office Location Validation
              With Range("B24", "B100").Validation
                  .Delete
                  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                      Formula1:="=TEST"
                  .IgnoreBlank = True
                  .InCellDropdown = True
                  .InputTitle = ""
                  .ErrorTitle = ""
                  .InputMessage = ""
                  .ErrorMessage = ""
                  .ShowInput = True
                  .ShowError = False
              End With
  
              'Member Name Validation
              With Range("D24", "D100").Validation
                  .Delete
                  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                      Formula1:="=TESTMEMBERS"
                  .IgnoreBlank = True
                  .InCellDropdown = True
                  .InputTitle = ""
                  .ErrorTitle = ""
                  .InputMessage = ""
                  .ErrorMessage = ""
                  .ShowInput = True
                  .ShowError = False
              End With
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/Hr4wJ.png" rel="nofollow noreferrer">Picture of how the Named Range is set-up</a></p>