Prevent List Box Reference from Changing when workbook closes?

[dim]

Hi,
I have a list box in Book1.xls which references a named range in Book2.xls.

Book2.xls is ALWAYS open before Book1.xls. I set my list box reference in
Book1 as 'Book2.xls'!MyRange - which displays the data fine.

However, when I next open Book1, the list box reference automatically shows
the full path to the named range i.e: 'C:\Program
Files\MyProgram\Book2.xls'!MyRange. I don't want it to, because the location
of the folders can change. I just always want it to reference 'Book2'!MyRange
which will always be open with it.

 

[JLatham]

You're not really going to be able to do away with that full reference. It
should only appear that way while Book2 is closed. Excel has no way of
knowing or being assured that you will always have Book2 open while Book1 is
open. Nor does Excel know if you've moved a file that is referenced - that's
what the Edit Links feature is for. I'd just try to make sure that I opened
Book1 before opening Book2.

 

[dim]

Thanks J,

I started thinking that and got to work removing all such links in my
workbooks, and writing it into the relevant Macros to import the relevant
data instead. I'm about 1/2 way through. :tired:

 

[JLatham]

Good luck with the transition from formulas to code.