I'm trying to set the rowsource for a listbox to use a named range in a
workbook that's external to the workbook where the listbox is defined.

Dim wb As Workbook
Set wb = Workbooks("Budget Account Table.xls")
lbPAAccount.RowSource = wb.Names("AccountAbbrev").RefersTo

The "Budget Account Table.xls" is open in the current workspace. Do I also
need to
Activate "Budget Account Table.xls"?

I'd try:

lbPAAccount.RowSource _
= wb.Names("AccountAbbrev").RefersToRange.address(ex ternal:=true)



cellist wrote:

I'm trying to set the rowsource for a listbox to use a named range in a
workbook that's external to the workbook where the listbox is defined.

Dim wb As Workbook
Set wb = Workbooks("Budget Account Table.xls")
lbPAAccount.RowSource = wb.Names("AccountAbbrev").RefersTo

The "Budget Account Table.xls" is open in the current workspace. Do I also
need to
Activate "Budget Account Table.xls"?

--

Dave Peterson

Dave, thanks for your reply.

Your suggestion initially failed in the same manner as my original code. The
problem with both was that I did not have the "Budget Account Table.xls"
open. I was under the impression that EXCEL would automatically open the file
when I did the RefersTo, but that apparently is not the case.

As long as I manually open "Budget Account Table.xls", both forms of the
RefersTo work. I also added wb.Activate; not certain whether that is
necessary.

To avoid having to remember to do the manual open, I saved the workspace and
can open the workspace instead of opening the main workbook. This is OK, but
I'd rather be able to open the main workbook and not have to manually open
the "Budget Account Table.xls" workbook. (I'm going to open a new topic for
that question.)

Phil

"Dave Peterson" wrote:

I'd try:

lbPAAccount.RowSource _
= wb.Names("AccountAbbrev").RefersToRange.address(ex ternal:=true)



cellist wrote:

I'm trying to set the rowsource for a listbox to use a named range in a
workbook that's external to the workbook where the listbox is defined.

Dim wb As Workbook
Set wb = Workbooks("Budget Account Table.xls")
lbPAAccount.RowSource = wb.Names("AccountAbbrev").RefersTo

The "Budget Account Table.xls" is open in the current workspace. Do I also
need to
Activate "Budget Account Table.xls"?

--

Dave Peterson