How to reference named range in VBA from an inactive sheet?

Question

I see this question asked and answered but I'm missing something! In order to use Named Ranges in my VBA I must activate the sheet before using the Named Range, otherwise it accesses the "same" range in the active sheet. I have:

• made sure that the named range scope is set to Workbook.
• fully qualified every range and cell method or property in my code

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).

    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

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?

Thanks in advance!

An update:

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

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.

I noticed that s.Range("NamePlates!MasterStoreNamePlates") 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 "storeCount" 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 "storeCount" constant).

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.

Answer 1

i am adding another answer as the original question was modified significantly. it looks to me that you are over-complicating a matter which is rather simple if you understand it.

Before i explain further, i think your approach with setting the ".RowSource" with the ".address" property of a range is incorrect because the address property does not contain a reference of the parent sheet so it will always refer to the active sheet even if you have gotten that address from a global wb range. see below:

i hope the below would be helpful to you to achieve what you want. No ranges! No activations! keep it simple. i have been working with vba 12 years and i can count in one hand the number of times i have used .activate & .select

I have initialized my form with 3 list boxes (i) static hardcoded range (ii) named range and (iii) dynamic named range.

Private Sub UserForm_Initialize()

  Dim addr1 As String
  Dim addr2 As String
  Dim addr3 As String

  addr1 = "LB!$A$1:$A$4"
  addr2 = "List.Items" ' named range defined as "=LB!$A$1:$A$3"
  addr3 = "List.Items.Dyn" ' dynamic named range defined as "=OFFSET(LB!$A$1,0,0,COUNTA(LB!$A:$A),1)"

  Me.testListBox1.RowSource = addr1
  Me.testListBox2.RowSource = addr2
  Me.testListBox3.RowSource = addr3

  Debug.Print Me.testListBox1.RowSource
  Debug.Print Me.testListBox2.RowSource
  Debug.Print Me.testListBox3.RowSource

End Sub

here is the Debug.Print result in the immediate window

In my user form i have three listboxes populated from the same cells but referenced differently in the .RowSource property. It also works regardless which is the active sheet.

here i am initializing the same form from another worksheet

Answer 2

why do you want to activate the sheets when you could possibly do:

listBoxStores.RowSource = s.Range(s.Cells(r1, c1), s.Cells(r2, c2)).Address

instead of:

With s
  listBoxStores.RowSource = .Range(.Cells(r1, c1), .Cells(r2, c2)).Address
End With

Answer 3

ThisWorkbook.Names("NamedRange").RefersToRange

This will return the range object of the named range.