Brian,

I called it famously kludgy, meaning that it was very kludgy, not that it
was actually famous.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Brian Murphy" wrote in message
...
Hello Bob,

Thanks for the reply. I'm curious about why you call the solution "famous".
Has this solution been discussed or otherwise posted on this group before?

Last night I had cooked up a much kludgier solution where I also detect the
presence of the sheet level name being returned in place of a book level
name, but then I insert a blank worksheet into the file to temporarily be
the first sheet, then delete the sheet after I'm done.

I like yours better, and I'm going to give it a try.

Thanks,

Brian


ps: We've been in Austin since 1993. It's hot in the summer, but we're not
exactly the cold weather sort.



"Bob Phillips" wrote in message
...
Hi Brian,

There must be a better way, but as I can't see it right now, I have come
up
with this famously kludgy solution - I delete the worksheet name then
re-instate it

Dim iPos As Long
Dim nme As String
Dim refersto As String
Debug.Print ActiveWorkbook.Names("Bob").Name
Debug.Print Evaluate(ActiveWorkbook.Names("Bob").refersto)
If InStr(1, ActiveWorkbook.Names("Bob"), "!") 0 Then
nme = ActiveWorkbook.Names("Bob").Name
refersto = ActiveWorkbook.Names("Bob").refersto
ActiveWorkbook.Names("Bob").Delete
End If
Debug.Print ActiveWorkbook.Names("Bob").Name
Debug.Print Evaluate(ActiveWorkbook.Names("Bob").refersto)
If nme < "" Then
ActiveWorkbook.Names.Add Name:=nme, _
refersto:=refersto
End If

How is Austin. Lived there in the late 80's, and loved the place,
especially
the music scene. Used to go and see Jimmy Dale Gilmore in a diner up
north,
great days.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Brian Murphy" wrote in message
...
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