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