Hello Jan Karel,

Thank you very much for the reply. I have resorted to what you described. Just a little while ago I posted a macro that deletes/undeletes the sheet level name that is interfering, but surprisingly that runs over 3 times slower than adding/deleting a temporary worksheet. So here's my new version that does the sheet shuffle.

I guess now I know that there is no easy solution to this other than a macro like this.

Your NameManager utility does in fact delete the correct book level name, whereas NameList will not. So you've evidently tackled this problem before.

Thanks,

Brian



Function myWorkbook_Names(thename$) As Variant
Dim l_routinename$: AddToStack l_routinename, "myWorkbook_Names"
'created this routine to work around "first sheet" problem 5/8/2004
Dim oldsheet$
Set myWorkbook_Names = Nothing
If IsNameDefinedAsBookLevel(thename) = False Then
'exit and return Nothing
ElseIf InStr(ActiveWorkbook.Names(thename).Name, "!") = 0 Then
Set myWorkbook_Names = ActiveWorkbook.Names(thename)
Else
'even though there's a book level name somewhere, Workbook.Names isn't returning it
oldsheet = ActiveSheet.Name
Worksheets.Add befo=Worksheets(1)
'now ActiveWorkbook.Names(thename) should return the correct book level name
Set myWorkbook_Names = ActiveWorkbook.Names(thename)
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Sheets(oldsheet).Activate
End If
RemoveFromStack l_routinename
End Function

"Jan Karel Pieterse" wrote in message m...
Hi Brian

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.

The only way to do this, is to activate another sheet when you want to
use the global name. As long as you are on a sheet that contains a
local name identical to the global one, you will access the local
version of the name.

So what you need to do is:

- check all names to discern whether there is a local duplicate
- If so, make sure another sheet is selected than the sheet with the
local name
- work on the global name.

Alternatively, if you always want to work on the global name, simply
insert a temporary sheet, then work with the global name, then remove
the temporary sheet.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com