How to Set VB ComboBox ListFillRange to a Defined Range in an external workbook

[Ken]

How does one set the ListFillRange for a ComboBox placed in a sheet in an
Excel Workbook to be filled from a defined range in another workbook. I am
hoping this can be done such that the external workbook does not need to be
open in order to fill the ComboBox.

I tried assigning the ListFillRange property for the ComboBox to the
following, where the range is located in "sheet1" of the external
"NewBook.xls" workbook.

'C:\[NewBook.xls]Sheet1'!$a$1:$a$10

However, on entering the above the ListFillRange property blanks, losing my
entry. Any ideas would be appreciated.

KSE

 

[Tom Ogilvy]

The workbook must be open.

Perhaps you could use a querytable to retrieve the data into the current
workbook and then use the results for the listfill range.