VBA ListBox populating with #N/A instead of named range

Question

I have this code to define the source range for multi-select ListBoxes on my worksheet.

Public Function ColourList() As Range
    Set ColourList = Range("Colours")
End Function

ColourList is then referenced in another part of the code where the ListBox is created.

.ListFillRange = ColourList().Address

In my current worksheet I have the named range "Colours" defined which refers to a Table in a different workbook.

The problem I am having is:

If the named range "colours" in the current workbook refers to a Table number in another workbook, then the ListBoxes on all sheets populate with #N/A equal to the number of rows in the Table.
If the named range in the current workbook refers to a named range in another workbook, then the ListBoxes on all sheets populate with blank rows equal to the number of rows in the named range.
If the named range in the current workbook refers to cells on Sheet1 and defined with scope of workbook, then the ListBoxes on Sheet1 populate correctly, however all other sheets populate with blank rows equal to the number of rows in the named range.

How can I get these ListBoxes to populate correctly on all sheets and from named ranges which refer to another workbook?

Why don't you populate an array from your 'Colours' range and use that array to populate your Listbox:? — Fernando J. Rivera, May 24 '21 at 18:34
The address property by default is just the range address - it does not include workbook and/or sheet names. You need to add the `external:=true` argument. — Rory, May 24 '21 at 18:36
https://stackoverflow.com/questions/28199576/listfillrange-from-another-sheet — Siddharth Rout, May 24 '21 at 20:00

0 Answers0