User Form referencing another sheet Run-time error '1004':

I am trying to get a user form list box to populate the values in a range from another sheet and getting this error. I have spent so much time working on it to no avail. I understand that excel cannot see the referenced sheets "Products" and "Locations", but I don't understand why.

Option Explicit
Private Sub UserForm_Initialize()
Dim LocationsList, ProductsList, Location, Product As Range

Set LocationsList = ActiveWorkbook.Sheets("Locations").Range("B2", Range("B2").End(xlDown))
Set ProductsList = ActiveWorkbook.Sheets("Products").Range("B2", Range("B2").End(xlDown))

For Each Location In LocationsList
    ListBoxLocations.AddItem Location.Value
Next

For Each Product In ProductsList
    ListBoxProducts.AddItem Product.Value
Next

End Sub

Solution

Use fully qualified range references

First of all it would be correct to declare all variables in the 1st code line As Range, otherwise they are seen As Variant:

   Dim LocationsList As Range, ProductsList As Range, Location As Range, Product As Range

2nd) The main issue raising Error 1004 is that a not qualified range reference Range("B2").End(xlDown) always refers to the range in the active worksheet. As in one of both settings the internal range refers to another sheet, you can't avoid error.

Further hint: Write a stop in between to test and change your sheet selection manually before running the userform. Therefore it's not correct to say that The first one seems to work, miraculously., it's only because the active worksheet may be the Locations sheet.

Working code

The long version of setting the first range to memory would be e.g.

 Set LocationsList = ThisWorkbook.Worksheets("Locations").Range("B2", ThisWorkbook.Worksheets("Locations").Range("B2").End(xlDown))

This could be abbreviated by using the sheet's CodeName such as e.g. Sheet1 (of course you can change it in the VB Editor's Tool Window ~> (Name) property to the normal Excel sheet name)

Set LocationsList = Sheet1.Range("B2", Sheet1.Range("B2").End(xlDown))

or use a With ... statement as shown below

Example code close to your OP

Private Sub UserForm_Initialize()
Dim LocationsList As Range, ProductsList As Range, Location As Range, Product As Range
With ThisWorkbook.Worksheets("Locations")
    Set LocationsList = .Range("B2", .Range("B2").End(xlDown))
End With
With ThisWorkbook.Worksheets("Products")
    Set ProductsList = .Range("B2", .Range("B2").End(xlDown))
End With

For Each Location In LocationsList
    ListBoxLocations.AddItem Location.Value
Next

For Each Product In ProductsList
    ListBoxProducts.AddItem Product.Value
Next
End Sub