title: How to reference named range in VBA from an inactive sheet?
body: <p>I see this question asked and answered but I'm missing something!  In order to use Named Ranges in my VBA I <em>must</em> activate the sheet before using the Named Range, otherwise it accesses the "same" range in the active sheet.  I have:</p>
  
  <ul>
  <li>made sure that the named range scope is set to Workbook.</li>
  <li>fully qualified every range and cell method or property in my code</li>
  </ul>
  
  <p>Here is the code.  It works fine if I put back in the worksheet.activate call but otherwise it references a range in the wrong sheet (the active one).</p>
  
  <pre><code>    Sub UserForm_Initialize()
     ' Application.ScreenUpdating = False
  
      Dim r1 As Long, c1 As Long, r2 As Long, c2 As Long, rng1 As Range, rng2 As Range, s As Worksheet, initSheet As Worksheet
  
      Set s = Sheets("NamePlates")
      Set rng1 = s.Range("MasterStoreNamePlates")
      Set initSheet = Application.ActiveSheet
  
      r1 = rng1.Row
      c1 = rng1.Column
      r2 = r1 + storeCount - 1
      c2 = c1
  
      's.Activate
      With s
          listBoxStores.RowSource = .Range(.Cells(r1, c1), .Cells(r2, c2)).Address
      End With
      'initSheet.Activate
  
     ' Application.ScreenUpdating = True
  
  End Sub
  </code></pre>
  
  <p>It's not too demanding to switch to a different sheet for a quick range lookup, but come on... what's the point of naming the ranges and making the name global and all the posts about "make sure to set the name to global" if I have to still go and switch the active sheet?</p>
  
  <p>Thanks in advance!</p>
  
  <p>An update:</p>
  
  <pre><code>Sub UserForm_Initialize()
  
      Application.ScreenUpdating = False
  
      Dim rng1 As Range, rng2 As Range, sInd As Long, initSheet As Worksheet, s As Worksheet
  
      sInd = Sheets("NamePlates").index
      Set s = Sheets(sInd)
      Set rng1 = s.Range("NamePlates!MasterStoreNamePlates")
      Set initSheet = Application.ActiveSheet
  
         listBoxStores.RowSource = rng1.Range(s.Cells(1, 1), s.Cells(storeCount - 1, 1)).Address
  
      Application.ScreenUpdating = True
  
  End Sub
  </code></pre>
  
  <p>Doesn't provide any different result.  It still accesses the active sheet.  If I change the active sheet before doing this, it retrieves the right data, otherwise it pulls the range from the active sheet.</p>
  
  <p>I noticed that <code>s.Range("NamePlates!MasterStoreNamePlates")</code> does get me the correct range... but then when I use that range and do something relative to it, I lose the sheet reference.  So I think maybe my solution will involve dynamically updating the named range and then this method should work.  Currently you can see that I'm using that constant <code>"storeCount"</code> which is not really a very good way to do things.  It is just kind of my hack for getting things up and running.  All the other suggested methods fail in my case for the same reason; even if they make a good reference to the correct range, I loose that sheet reference when I start doing anything that defines the range I want, which is 12 rows longer than the named range (thus the <code>"storeCount"</code> constant).</p>
  
  <p>If I can't figure out how to do a dynamically changing named range I'll post a new question but I've seen some things about that so I should be able to get that working and I'll post my solution here... or if someone beats me to it I'll accept that answer even though it's a little different from the OP.</p>