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