I couldn't reproduce the behavior in Excel 2002 either.
I defined a sheet-level name referring to the range "Sheet1_Test", and a
book-level name referring to the range "Test" on Sheet2. Sheet1 is
physically located before Sheet2.
The address of the range returned by:
ThisWorkbook.Names("Test").ReferesToRange
was the Sheet2 address (obviously, I used a different local address for the
named range each sheet).
--
Vasant
"Brian Murphy" wrote in message
...
Hello Dave,
Thanks very much for the reply.
The output you showed from your macro tells me that the first sheet in the
tab order is Sheet2, and this is the sheet which contains the booklevel
instance of the Name. This situation won't exhibit the problem.
Use your mouse to change the sheet order. Have the first sheet contain a
sheet level instance. I did this, and with your macro got the following:
3 sheets in the file
Sheet order is: Book_level, Sheet_level_1, Sheet_Level_2
wkbk level from: Book_level refers to: Book_level
Not a sheet level name in: Book_level
-------------
wkbk level from: Sheet_level_1 refers to: Book_level
Sheet level also in: [Book1]Sheet_level_1!$A$1
-------------
wkbk level from: Sheet_level_2 refers to: Book_level
Sheet level also in: [Book1]Sheet_level_2!$A$1
-------------
Sheet order changed to: Sheet_level_1, Book_level, Sheet_Level_2
wkbk level from: Sheet_level_1 refers to: Sheet_level_1
Sheet level also in: [Book1]Sheet_level_1!$A$1
-------------
wkbk level from: Book_level refers to: Sheet_level_1
Not a sheet level name in: Book_level
-------------
wkbk level from: Sheet_level_2 refers to: Sheet_level_1
Sheet level also in: [Book1]Sheet_level_2!$A$1
-------------
Darn it! Now the book level name is wrong no matter which sheet is active.
The NameManager utility seems to get it right. The j-walk namelist utility,
for just one example, does not. That's why I posted to the group in hopes
of learning the right way to do this.
This odd behavior becomes a real problem when trying to do something like
delete a book level name. The delete method will delete the wrong object,
even when it's fully qualified.
Brian Murphy
Austin, Texas
"Dave Peterson" wrote in message
...
Are you sure?
I'm using xl2002 and couldn't duplicate that.
Option Explicit
Sub testme02()
Dim wks As Worksheet
Dim testRng As Range
For Each wks In ActiveWorkbook.Worksheets
wks.Activate
Debug.Print "wkbk level from: " & wks.Name & " refers to: " & _
ActiveWorkbook.Names("somename").RefersToRange.Par ent.Name
Set testRng = Nothing
On Error Resume Next
Set testRng = wks.Names("somename").RefersToRange
On Error GoTo 0
If testRng Is Nothing Then
Debug.Print "Not a sheet level name in: " & wks.Name
Else
Debug.Print "Sheet level also in: " & _
testRng.Address(external:=True)
End If
Debug.Print "-------------"
Next wks
End Sub
I got this back:
wkbk level from: Sheet2 refers to: Sheet2
Not a sheet level name in: Sheet2
-------------
wkbk level from: Sheet6 refers to: Sheet2
Not a sheet level name in: Sheet6
-------------
wkbk level from: Sheet5 refers to: Sheet2
Not a sheet level name in: Sheet5
-------------
wkbk level from: Sheet4 refers to: Sheet2
Not a sheet level name in: Sheet4
-------------
wkbk level from: Sheet3 refers to: Sheet2
Not a sheet level name in: Sheet3
-------------
wkbk level from: Sheet1 refers to: Sheet2
Sheet level also in: [book1.xls]Sheet1!$B$9:$D$18
-------------
It worked the same way with or without the wks.activate.
The best utility that I've ever seen for working with names is Jan Karel
Pieterse's (with Charles Williams and Matthew Henson) Name Manager.
You can find it at:
NameManager.Zip from http://www.bmsltd.ie/mvp
You get lots of options and can see differences very easily. It's well
worth
the download.
You can localize and globalize names using this, too.
Brian Murphy wrote:
I'm having trouble finding a fail safe syntax to return a Name object
that refers to a cell range.
set obj = ActiveWorkbook.Names(somename)
Here "somename" is a book level name that refers to a range of cells.
The statement above returns a range object for the cells.
Well, sometimes, but not all the time.
If the first sheet in the workbook also contains a sheet level name
called "somename", then the above statement returns those cells instead.
So, I'm hoping there is some sort of statement syntax that will work
regardless of whether the first sheet has a like named sheet level name.
Thanks,
Brian Murphy
Austin, Texas
--
Dave Peterson