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

Well i dont know how to do this but i thought i would do the leg wor
for you....this has been ripped off from Chip Pearsons site....h
writes:-

It is very simple to retrieve sheet names in VBA. They are stored i
two collection objects in the ActiveWorkbook object: the Sheet
collection and the Worksheets collection. The Sheets collectio
contains both worksheets and chart sheets. The Worksheets collectio
contains only worksheets.

To retrieve the name of the first sheet in the workbook, use

Public Function FirstSheetName()
FirstSheetName = Sheets(1).Name
End Function

To retrieve the name of the last sheet in the workbook, use

Public Function LastSheetName()
LastSheetName = Sheets(Sheets.Count).Name
End Function

You can return an array of all the sheet names with the following

Public Function AllSheetNames()
Dim Arr() As String
Dim I as Integer
Redim Arr(Sheets.Count-1)
For I = 0 To Sheets.Count - 1
Arr(i) = Sheets(I+1).Name
Next I
AllSheetNames = Arr ' return a row array OR
AllSheetNames = Application.Worksheetfunction.Transpose(Arr)
' return a column array
End Function


HTH

Simo

--
Message posted from http://www.ExcelForum.com

why not use ActiveShee

----- 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 Murph
Austin, Texa

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

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

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

Hello Simon,

Thanks for the post, but I'm not after sheet names. The problem concerns defined Names. Please see my other replies.

Brian


"Simon Lloyd " wrote in message ...
Well i dont know how to do this but i thought i would do the leg work
for you....this has been ripped off from Chip Pearsons site....he
writes:-

It is very simple to retrieve sheet names in VBA. They are stored in
two collection objects in the ActiveWorkbook object: the Sheets
collection and the Worksheets collection. The Sheets collection
contains both worksheets and chart sheets. The Worksheets collection
contains only worksheets.

To retrieve the name of the first sheet in the workbook, use

Public Function FirstSheetName()
FirstSheetName = Sheets(1).Name
End Function

To retrieve the name of the last sheet in the workbook, use

Public Function LastSheetName()
LastSheetName = Sheets(Sheets.Count).Name
End Function

You can return an array of all the sheet names with the following

Public Function AllSheetNames()
Dim Arr() As String
Dim I as Integer
Redim Arr(Sheets.Count-1)
For I = 0 To Sheets.Count - 1
Arr(i) = Sheets(I+1).Name
Next I
AllSheetNames = Arr ' return a row array OR
AllSheetNames = Application.Worksheetfunction.Transpose(Arr)
' return a column array
End Function


HTH

Simon


---
Message posted from http://www.ExcelForum.com/

In my testing ActiveSheet.Names doesn't return any book level names.

Brian



"chris" wrote in message ...
why not use ActiveSheet

----- 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

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

Brian,

This odd behavior becomes a real problem when trying
to do something like delete a book level name.

Do I take it you want to delete a book level name but keep
any/all sheet level names of the same name?

Trouble is Sheet names are both members both of the
worksheet.names collection and the workbook.names
collection. Maybe this might work:

Sub DelBookName()
Dim wk As Worksheet, nm As Name
Dim sName As String
sName = "somename"

For Each nm In ActiveWorkbook.Names

'if it's a sheet name should return "Sheetname!somename"
Debug.Print nm.Name

'does not include "Sheetname! so should be safe to delete
If nm.Name = sName Then nm.Delete
Next

End Sub

Regards,
Peter

<snip

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

Ahhh. I get it and remember a post from Jan Karel Pieterse (IIRC) about the
same problem.

I think his solution was to look for a worksheet level name first. If he found
one, the move to a different sheet.

But maybe you could just add a dummy worksheet as the first one. Delete it when
you're done.

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim testRng As Range
Dim dummyWks As Worksheet

Set dummyWks = Worksheets.Add(befo=Worksheets(1))

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = dummyWks.Name Then
'do nothing
Else
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 "-------------"
End If

Next wks

Application.DisplayAlerts = False
dummyWks.Delete
Application.DisplayAlerts = True

End Sub

Befo
wkbk level from: sheetlevel1 refers to: sheetlevel1
Sheet level also in: [book1]sheetlevel1!$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to: sheetlevel1
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to: sheetlevel1
Sheet level also in: [book1]sheetlevel2!$D$5:$H$15
-------------


After:

wkbk level from: sheetlevel1 refers to: Wkbk_level
Sheet level also in: [book1]sheetlevel1!$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to: Wkbk_level
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to: Wkbk_level
Sheet level also in: [book1]sheetlevel2!$D$5:$H$15
-------------

==============

I think Jan Karel (et al) have to be much more careful. The workbook's
structure could be protected and that would eliminate the ability to add a
sheet.

This might not be a factor for you.


=====
And depending on what you're doing, maybe you can go through the names
collection:

Option Explicit
Sub testme02b()
Dim myName As Name
Dim myStr As String
Dim myRng As Range

myStr = "somename"

Set myRng = Nothing
For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) = LCase(myStr) Then
'use this one!
Set myRng = myName.RefersToRange
Exit For
End If
Next myName

If myRng Is Nothing Then
Debug.Print "No Global name: " & myStr
Else
Debug.Print myRng.Address(external:=True)
End If

End Sub

This consistently gave me:
[book1]Wkbk_level!$E$8:$I$20

No matter the order of the sheets.

(Yeah, I missed that bottom part in your original post. Sorry.)







Brian Murphy wrote:

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


--

Dave Peterson

I just tried your approach with the following loop, and o.Delete deleted the wrong name object, even though it was pointing to the correct book level name object! For this to occur "somename" needs to be sheet level on the first sheet, and book level on another sheet.

Sub test2()
Dim o
For Each o In ActiveWorkbook.Names
If o.Name = "somename" Then o.Delete
Next
End Sub


Brian



"Peter T" wrote in message ...
Brian,

This odd behavior becomes a real problem when trying
to do something like delete a book level name.

Do I take it you want to delete a book level name but keep
any/all sheet level names of the same name?

Trouble is Sheet names are both members both of the
worksheet.names collection and the workbook.names
collection. Maybe this might work:

Sub DelBookName()
Dim wk As Worksheet, nm As Name
Dim sName As String
sName = "somename"

For Each nm In ActiveWorkbook.Names

'if it's a sheet name should return "Sheetname!somename"
Debug.Print nm.Name

'does not include "Sheetname! so should be safe to delete
If nm.Name = sName Then nm.Delete
Next

End Sub

Regards,
Peter

<snip

No go for me (excel 2002, but it doesn't matter).

Starting with a blank 3 sheet workbook.
I use Ctrl-F3 to define a name on the 1st sheet as "Sheet1!test"
I use Ctrl-F3 to define a name on the 2nd sheet as "test"

Then in the immediate window I get this

?activeworkbook.names("test")
=Sheet1!$A$1

It doesn't matter which sheet is the activesheet.
But then move Sheet1 out of the 1st spot and I get this

?activeworkbook.names("test")
=Sheet2!$A$1

The only difference is the ordering of the worksheets.

I think activeworkbook.names("test") should give you the same correct name object regardless of the sheet order.

Brian



"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ...
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

Here's what I've arrived at using the "famous" workaround mentioned by Bob Phillips. It goes for deleting and undeleting just a Name instead of a Worksheet. Could use some error checking to make it more robust. I don't think Hidden sheets will cause problems. Protected sheets might be another matter. Seems to do the job in limited testing so far.

It's a far cry from just a simple syntax fix I was hoping for. I wonder how Jan Karel handled this.

Brian


Option Compare Text

Function myWorkbook_Names(thename$) As Variant
'created this routine to work around "first sheet" problem 5/7/2004
Dim nme$, refersto$
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
With ActiveWorkbook.Names(thename)
nme = .Name
refersto = .refersto
.Delete
End With
'now ActiveWorkbook.Names(thename) should return the correct book level name
Set myWorkbook_Names = ActiveWorkbook.Names(thename)
'put back the deleted sheet level name
ActiveWorkbook.Names.Add Name:=nme, refersto:=refersto
End If
End Function

Function IsNameDefinedAsBookLevel(thename$) As Boolean
Dim o
For Each o In ActiveWorkbook.Names
If o.Name = thename Then
IsNameDefinedAsBookLevel = True
Exit For
End If
Next
End Function


"Dave Peterson" wrote in message ...
Ahhh. I get it and remember a post from Jan Karel Pieterse (IIRC) about the
same problem.

I think his solution was to look for a worksheet level name first. If he found
one, the move to a different sheet.

But maybe you could just add a dummy worksheet as the first one. Delete it when
you're done.

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim testRng As Range
Dim dummyWks As Worksheet

Set dummyWks = Worksheets.Add(befo=Worksheets(1))

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = dummyWks.Name Then
'do nothing
Else
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 "-------------"
End If

Next wks

Application.DisplayAlerts = False
dummyWks.Delete
Application.DisplayAlerts = True

End Sub

Befo
wkbk level from: sheetlevel1 refers to: sheetlevel1
Sheet level also in: [book1]sheetlevel1!$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to: sheetlevel1
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to: sheetlevel1
Sheet level also in: [book1]sheetlevel2!$D$5:$H$15
-------------


After:

wkbk level from: sheetlevel1 refers to: Wkbk_level
Sheet level also in: [book1]sheetlevel1!$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to: Wkbk_level
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to: Wkbk_level
Sheet level also in: [book1]sheetlevel2!$D$5:$H$15
-------------

==============

I think Jan Karel (et al) have to be much more careful. The workbook's
structure could be protected and that would eliminate the ability to add a
sheet.

This might not be a factor for you.


=====
And depending on what you're doing, maybe you can go through the names
collection:

Option Explicit
Sub testme02b()
Dim myName As Name
Dim myStr As String
Dim myRng As Range

myStr = "somename"

Set myRng = Nothing
For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) = LCase(myStr) Then
'use this one!
Set myRng = myName.RefersToRange
Exit For
End If
Next myName

If myRng Is Nothing Then
Debug.Print "No Global name: " & myStr
Else
Debug.Print myRng.Address(external:=True)
End If

End Sub

This consistently gave me:
[book1]Wkbk_level!$E$8:$I$20

No matter the order of the sheets.

(Yeah, I missed that bottom part in your original post. Sorry.)







Brian Murphy wrote:

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


--

Dave Peterson

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

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

Sorry, you are correct. I had the sheets reversed.

--

Vasant

"Brian Murphy" wrote in message
...
Here's what I've arrived at using the "famous" workaround mentioned by Bob
Phillips. It goes for deleting and undeleting just a Name instead of a
Worksheet. Could use some error checking to make it more robust. I don't
think Hidden sheets will cause problems. Protected sheets might be another
matter. Seems to do the job in limited testing so far.

It's a far cry from just a simple syntax fix I was hoping for. I wonder how
Jan Karel handled this.

Brian


Option Compare Text

Function myWorkbook_Names(thename$) As Variant
'created this routine to work around "first sheet" problem 5/7/2004
Dim nme$, refersto$
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
With ActiveWorkbook.Names(thename)
nme = .Name
refersto = .refersto
.Delete
End With
'now ActiveWorkbook.Names(thename) should return the correct book
level name
Set myWorkbook_Names = ActiveWorkbook.Names(thename)
'put back the deleted sheet level name
ActiveWorkbook.Names.Add Name:=nme, refersto:=refersto
End If
End Function

Function IsNameDefinedAsBookLevel(thename$) As Boolean
Dim o
For Each o In ActiveWorkbook.Names
If o.Name = thename Then
IsNameDefinedAsBookLevel = True
Exit For
End If
Next
End Function


"Dave Peterson" wrote in message
...
Ahhh. I get it and remember a post from Jan Karel Pieterse (IIRC) about
the
same problem.

I think his solution was to look for a worksheet level name first. If he
found
one, the move to a different sheet.

But maybe you could just add a dummy worksheet as the first one. Delete
it when
you're done.

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim testRng As Range
Dim dummyWks As Worksheet

Set dummyWks = Worksheets.Add(befo=Worksheets(1))

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = dummyWks.Name Then
'do nothing
Else
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 "-------------"
End If

Next wks

Application.DisplayAlerts = False
dummyWks.Delete
Application.DisplayAlerts = True

End Sub

Befo
wkbk level from: sheetlevel1 refers to: sheetlevel1
Sheet level also in: [book1]sheetlevel1!$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to: sheetlevel1
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to: sheetlevel1
Sheet level also in: [book1]sheetlevel2!$D$5:$H$15
-------------


After:

wkbk level from: sheetlevel1 refers to: Wkbk_level
Sheet level also in: [book1]sheetlevel1!$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to: Wkbk_level
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to: Wkbk_level
Sheet level also in: [book1]sheetlevel2!$D$5:$H$15
-------------

==============

I think Jan Karel (et al) have to be much more careful. The workbook's
structure could be protected and that would eliminate the ability to add a
sheet.

This might not be a factor for you.


=====
And depending on what you're doing, maybe you can go through the names
collection:

Option Explicit
Sub testme02b()
Dim myName As Name
Dim myStr As String
Dim myRng As Range

myStr = "somename"

Set myRng = Nothing
For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) = LCase(myStr) Then
'use this one!
Set myRng = myName.RefersToRange
Exit For
End If
Next myName

If myRng Is Nothing Then
Debug.Print "No Global name: " & myStr
Else
Debug.Print myRng.Address(external:=True)
End If

End Sub

This consistently gave me:
[book1]Wkbk_level!$E$8:$I$20

No matter the order of the sheets.

(Yeah, I missed that bottom part in your original post. Sorry.)







Brian Murphy wrote:

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


--

Dave Peterson

If you were only retrieving the value for a few names, didn't going through the
workbook names collection work for you?


Brian Murphy wrote:

Here's what I've arrived at using the "famous" workaround mentioned by Bob Phillips. It goes for deleting and undeleting just a Name instead of a Worksheet. Could use some error checking to make it more robust. I don't think Hidden sheets will cause problems. Protected sheets might be another matter. Seems to do the job in limited testing so far.

It's a far cry from just a simple syntax fix I was hoping for. I wonder how Jan Karel handled this.

Brian

Option Compare Text

Function myWorkbook_Names(thename$) As Variant
'created this routine to work around "first sheet" problem 5/7/2004
Dim nme$, refersto$
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
With ActiveWorkbook.Names(thename)
nme = .Name
refersto = .refersto
.Delete
End With
'now ActiveWorkbook.Names(thename) should return the correct book level name
Set myWorkbook_Names = ActiveWorkbook.Names(thename)
'put back the deleted sheet level name
ActiveWorkbook.Names.Add Name:=nme, refersto:=refersto
End If
End Function

Function IsNameDefinedAsBookLevel(thename$) As Boolean
Dim o
For Each o In ActiveWorkbook.Names
If o.Name = thename Then
IsNameDefinedAsBookLevel = True
Exit For
End If
Next
End Function

"Dave Peterson" wrote in message ...
Ahhh. I get it and remember a post from Jan Karel Pieterse (IIRC) about the
same problem.

I think his solution was to look for a worksheet level name first. If he found
one, the move to a different sheet.

But maybe you could just add a dummy worksheet as the first one. Delete it when
you're done.

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim testRng As Range
Dim dummyWks As Worksheet

Set dummyWks = Worksheets.Add(befo=Worksheets(1))

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = dummyWks.Name Then
'do nothing
Else
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 "-------------"
End If

Next wks

Application.DisplayAlerts = False
dummyWks.Delete
Application.DisplayAlerts = True

End Sub

Befo
wkbk level from: sheetlevel1 refers to: sheetlevel1
Sheet level also in: [book1]sheetlevel1!$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to: sheetlevel1
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to: sheetlevel1
Sheet level also in: [book1]sheetlevel2!$D$5:$H$15
-------------


After:

wkbk level from: sheetlevel1 refers to: Wkbk_level
Sheet level also in: [book1]sheetlevel1!$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to: Wkbk_level
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to: Wkbk_level
Sheet level also in: [book1]sheetlevel2!$D$5:$H$15
-------------

==============

I think Jan Karel (et al) have to be much more careful. The workbook's
structure could be protected and that would eliminate the ability to add a
sheet.

This might not be a factor for you.


=====
And depending on what you're doing, maybe you can go through the names
collection:

Option Explicit
Sub testme02b()
Dim myName As Name
Dim myStr As String
Dim myRng As Range

myStr = "somename"

Set myRng = Nothing
For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) = LCase(myStr) Then
'use this one!
Set myRng = myName.RefersToRange
Exit For
End If
Next myName

If myRng Is Nothing Then
Debug.Print "No Global name: " & myStr
Else
Debug.Print myRng.Address(external:=True)
End If

End Sub

This consistently gave me:
[book1]Wkbk_level!$E$8:$I$20

No matter the order of the sheets.

(Yeah, I missed that bottom part in your original post. Sorry.)







Brian Murphy wrote:

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


--

Dave Peterson


--

Dave Peterson

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

I usually need a range object for what the Name refers to. Although sometimes I do need to delete the workbook level Name object.

It was pretty bad when a macro of mine would go to delete a fully qualified book level name, only to find out later that it was a sheet level name that got deleted.

Brian



"Dave Peterson" wrote in message ...
If you were only retrieving the value for a few names, didn't going through the
workbook names collection work for you?


Brian Murphy wrote:

Here's what I've arrived at using the "famous" workaround mentioned by Bob Phillips. It goes for deleting and undeleting just a Name instead of a Worksheet. Could use some error checking to make it more robust. I don't think Hidden sheets will cause problems. Protected sheets might be another matter. Seems to do the job in limited testing so far.

It's a far cry from just a simple syntax fix I was hoping for. I wonder how Jan Karel handled this.

Brian

Option Compare Text

Function myWorkbook_Names(thename$) As Variant
'created this routine to work around "first sheet" problem 5/7/2004
Dim nme$, refersto$
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
With ActiveWorkbook.Names(thename)
nme = .Name
refersto = .refersto
.Delete
End With
'now ActiveWorkbook.Names(thename) should return the correct book level name
Set myWorkbook_Names = ActiveWorkbook.Names(thename)
'put back the deleted sheet level name
ActiveWorkbook.Names.Add Name:=nme, refersto:=refersto
End If
End Function

Function IsNameDefinedAsBookLevel(thename$) As Boolean
Dim o
For Each o In ActiveWorkbook.Names
If o.Name = thename Then
IsNameDefinedAsBookLevel = True
Exit For
End If
Next
End Function

"Dave Peterson" wrote in message ...
Ahhh. I get it and remember a post from Jan Karel Pieterse (IIRC) about the
same problem.

I think his solution was to look for a worksheet level name first. If he found
one, the move to a different sheet.

But maybe you could just add a dummy worksheet as the first one. Delete it when
you're done.

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim testRng As Range
Dim dummyWks As Worksheet

Set dummyWks = Worksheets.Add(befo=Worksheets(1))

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = dummyWks.Name Then
'do nothing
Else
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 "-------------"
End If

Next wks

Application.DisplayAlerts = False
dummyWks.Delete
Application.DisplayAlerts = True

End Sub

Befo
wkbk level from: sheetlevel1 refers to: sheetlevel1
Sheet level also in: [book1]sheetlevel1!$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to: sheetlevel1
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to: sheetlevel1
Sheet level also in: [book1]sheetlevel2!$D$5:$H$15
-------------


After:

wkbk level from: sheetlevel1 refers to: Wkbk_level
Sheet level also in: [book1]sheetlevel1!$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to: Wkbk_level
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to: Wkbk_level
Sheet level also in: [book1]sheetlevel2!$D$5:$H$15
-------------

==============

I think Jan Karel (et al) have to be much more careful. The workbook's
structure could be protected and that would eliminate the ability to add a
sheet.

This might not be a factor for you.


=====
And depending on what you're doing, maybe you can go through the names
collection:

Option Explicit
Sub testme02b()
Dim myName As Name
Dim myStr As String
Dim myRng As Range

myStr = "somename"

Set myRng = Nothing
For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) = LCase(myStr) Then
'use this one!
Set myRng = myName.RefersToRange
Exit For
End If
Next myName

If myRng Is Nothing Then
Debug.Print "No Global name: " & myStr
Else
Debug.Print myRng.Address(external:=True)
End If

End Sub

This consistently gave me:
[book1]Wkbk_level!$E$8:$I$20

No matter the order of the sheets.

(Yeah, I missed that bottom part in your original post. Sorry.)







Brian Murphy wrote:

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


--

Dave Peterson


--

Dave Peterson

In a private reply:

If you were looking for the range that global name referred to, I thought that
this suggestion worked ok. Did it not work for you?

Option Explicit
Sub testme02b()
Dim myName As Name
Dim myStr As String
Dim myRng As Range

myStr = "somename"

Set myRng = Nothing
For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) = LCase(myStr) Then
'use this one!
Set myRng = myName.RefersToRange
Exit For
End If
Next myName

If myRng Is Nothing Then
Debug.Print "No Global name: " & myStr
Else
Debug.Print myRng.Address(external:=True)
End If

End Sub

Brian Murphy wrote:

I usually need a range object for what the Name refers to. Although sometimes I do need to delete the workbook level Name object.

It was pretty bad when a macro of mine would go to delete a fully qualified book level name, only to find out later that it was a sheet level name that got deleted.

Brian

"Dave Peterson" wrote in message ...
If you were only retrieving the value for a few names, didn't going through the
workbook names collection work for you?


Brian Murphy wrote:

Here's what I've arrived at using the "famous" workaround mentioned by Bob Phillips. It goes for deleting and undeleting just a Name instead of a Worksheet. Could use some error checking to make it more robust. I don't think Hidden sheets will cause problems. Protected sheets might be another matter. Seems to do the job in limited testing so far.

It's a far cry from just a simple syntax fix I was hoping for. I wonder how Jan Karel handled this.

Brian

Option Compare Text

Function myWorkbook_Names(thename$) As Variant
'created this routine to work around "first sheet" problem 5/7/2004
Dim nme$, refersto$
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
With ActiveWorkbook.Names(thename)
nme = .Name
refersto = .refersto
.Delete
End With
'now ActiveWorkbook.Names(thename) should return the correct book level name
Set myWorkbook_Names = ActiveWorkbook.Names(thename)
'put back the deleted sheet level name
ActiveWorkbook.Names.Add Name:=nme, refersto:=refersto
End If
End Function

Function IsNameDefinedAsBookLevel(thename$) As Boolean
Dim o
For Each o In ActiveWorkbook.Names
If o.Name = thename Then
IsNameDefinedAsBookLevel = True
Exit For
End If
Next
End Function

"Dave Peterson" wrote in message ...
Ahhh. I get it and remember a post from Jan Karel Pieterse (IIRC) about the
same problem.

I think his solution was to look for a worksheet level name first. If he found
one, the move to a different sheet.

But maybe you could just add a dummy worksheet as the first one. Delete it when
you're done.

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim testRng As Range
Dim dummyWks As Worksheet

Set dummyWks = Worksheets.Add(befo=Worksheets(1))

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = dummyWks.Name Then
'do nothing
Else
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 "-------------"
End If

Next wks

Application.DisplayAlerts = False
dummyWks.Delete
Application.DisplayAlerts = True

End Sub

Befo
wkbk level from: sheetlevel1 refers to: sheetlevel1
Sheet level also in: [book1]sheetlevel1!$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to: sheetlevel1
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to: sheetlevel1
Sheet level also in: [book1]sheetlevel2!$D$5:$H$15
-------------


After:

wkbk level from: sheetlevel1 refers to: Wkbk_level
Sheet level also in: [book1]sheetlevel1!$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to: Wkbk_level
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to: Wkbk_level
Sheet level also in: [book1]sheetlevel2!$D$5:$H$15
-------------

==============

I think Jan Karel (et al) have to be much more careful. The workbook's
structure could be protected and that would eliminate the ability to add a
sheet.

This might not be a factor for you.


=====
And depending on what you're doing, maybe you can go through the names
collection:

Option Explicit
Sub testme02b()
Dim myName As Name
Dim myStr As String
Dim myRng As Range

myStr = "somename"

Set myRng = Nothing
For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) = LCase(myStr) Then
'use this one!
Set myRng = myName.RefersToRange
Exit For
End If
Next myName

If myRng Is Nothing Then
Debug.Print "No Global name: " & myStr
Else
Debug.Print myRng.Address(external:=True)
End If

End Sub

This consistently gave me:
[book1]Wkbk_level!$E$8:$I$20

No matter the order of the sheets.

(Yeah, I missed that bottom part in your original post. Sorry.)







Brian Murphy wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave,

First thought when I looked at your Testme02b() was that
it was pretty much the same as my DelBookName(). Only
difference, yours to reference the named range, mine to
delete the name.

I was surprised when Brian came back and said mine didn't
work. But he was right, in the particular scenario as
outlined by Jan Karel.

With similar Sheet & Book level names, referring to
different ranges, on the active sheet, try the
extra "delete" line in your sub:

'code
Set myRng = myName.RefersToRange
myName.Delete ' new line
Exit For
'code

Your "myRng" is correctly referenced to the book level
range, but the wrong name is deleted (ie the sheet level
name). I think deleting the Book level name was one of
Brian's objectives.

Regards,
Peter


-----Original Message-----
In a private reply:

If you were looking for the range that global name
referred to, I thought that
this suggestion worked ok. Did it not work for you?

Option Explicit
Sub testme02b()
Dim myName As Name
Dim myStr As String
Dim myRng As Range

myStr = "somename"

Set myRng = Nothing
For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) = LCase(myStr) Then
'use this one!
Set myRng = myName.RefersToRange
Exit For
End If
Next myName

If myRng Is Nothing Then
Debug.Print "No Global name: " & myStr
Else
Debug.Print myRng.Address(external:=True)
End If

End Sub

Brian Murphy wrote:

I usually need a range object for what the Name refers
to. Although sometimes I do need to delete the workbook
level Name object.

It was pretty bad when a macro of mine would go to
delete a fully qualified book level name, only to find out
later that it was a sheet level name that got deleted.

Brian

"Dave Peterson" wrote in message
...
If you were only retrieving the value for a few
names, didn't going through the
workbook names collection work for you?


Brian Murphy wrote:

Here's what I've arrived at using the "famous"
workaround mentioned by Bob Phillips. It goes for
deleting and undeleting just a Name instead of a
Worksheet. Could use some error checking to make it more
robust. I don't think Hidden sheets will cause problems.
Protected sheets might be another matter. Seems to do the
job in limited testing so far.

It's a far cry from just a simple syntax fix I was
hoping for. I wonder how Jan Karel handled this.

Brian

Option Compare Text

Function myWorkbook_Names(thename$) As Variant
'created this routine to work around "first sheet"
problem 5/7/2004
Dim nme$, refersto$
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
With ActiveWorkbook.Names(thename)
nme = .Name
refersto = .refersto
.Delete
End With
'now ActiveWorkbook.Names(thename) should
return the correct book level name
Set myWorkbook_Names = ActiveWorkbook.Names
(thename)
'put back the deleted sheet level name
ActiveWorkbook.Names.Add Name:=nme,
refersto:=refersto
End If
End Function

Function IsNameDefinedAsBookLevel(thename$) As
Boolean
Dim o
For Each o In ActiveWorkbook.Names
If o.Name = thename Then
IsNameDefinedAsBookLevel = True
Exit For
End If
Next
End Function

"Dave Peterson" wrote in message
...
Ahhh. I get it and remember a post from Jan
Karel Pieterse (IIRC) about the
same problem.

I think his solution was to look for a worksheet
level name first. If he found
one, the move to a different sheet.

But maybe you could just add a dummy worksheet as
the first one. Delete it when
you're done.

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim testRng As Range
Dim dummyWks As Worksheet

Set dummyWks = Worksheets.Add
(befo=Worksheets(1))

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = dummyWks.Name Then
'do nothing
Else
wks.Activate
Debug.Print "wkbk level from: " &
wks.Name & " refers to: " & _

ActiveWorkbook.Names
("somename").RefersToRange.Parent.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 "-------------"
End If

Next wks

Application.DisplayAlerts = False
dummyWks.Delete
Application.DisplayAlerts = True

End Sub

Befo
wkbk level from: sheetlevel1 refers to:
sheetlevel1
Sheet level also in: [book1]sheetlevel1!$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to:
sheetlevel1
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to:
sheetlevel1
Sheet level also in: [book1]sheetlevel2!$D$5:$H$15
-------------


After:

wkbk level from: sheetlevel1 refers to:
Wkbk_level
Sheet level also in: [book1]sheetlevel1!$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to: Wkbk_level
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to:
Wkbk_level
Sheet level also in: [book1]sheetlevel2!$D$5:$H$15
-------------

==============

I think Jan Karel (et al) have to be much more
careful. The workbook's
structure could be protected and that would
eliminate the ability to add a
sheet.

This might not be a factor for you.


=====
And depending on what you're doing, maybe you can
go through the names
collection:

Option Explicit
Sub testme02b()
Dim myName As Name
Dim myStr As String
Dim myRng As Range

myStr = "somename"

Set myRng = Nothing
For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) = LCase(myStr) Then
'use this one!
Set myRng = myName.RefersToRange
Exit For
End If
Next myName

If myRng Is Nothing Then
Debug.Print "No Global name: " & myStr
Else
Debug.Print myRng.Address(external:=True)
End If

End Sub

This consistently gave me:
[book1]Wkbk_level!$E$8:$I$20

No matter the order of the sheets.

(Yeah, I missed that bottom part in your original
post. Sorry.)







Brian Murphy wrote:

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.Parent.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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

.

Hi Brian,

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

Of course. I can tell you that has been a puzzle to solve...

Regards,

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

Hello Dave,

There are times when I need to delete the global name object. When all I need is the range object, yes, the loop approach works. In fact, this same loop is part of my final macro-based solution to getting a valid Name object for a book level name.

I still would like to think that there is some way to strong arm excel in to giving you a valid object pointer for a book level Name without having to resort to a complicated and time consuming macro approach.

Suppose you have a sheet level Name on every sheet except for one, and on that one it's book level.

msgbox range("book_level!somename") this displays the right cell
msgbox range("book_level!somename").Name this displays the right .refersto property
msgbox range("book_level!somename").Name.Name this displays the right Name property
range("book_level!somename").Name.Delete this deletes the wrong Name object

The last line will at times delete a sheet level Name object on one of the other sheets.

What I'd like is to get a Name object for which all properties and methods work like they're supposed to, and not have to worry about which sheet I'm on, or which sheet comes first, etc.

Darn it. I thought my macro did this for me, but I just used it to delete a book level name object and it deleted one of the sheet level objects instead (in this case the one on the activesheet).

Well, I've been running myself ragged on this. I think now I see that Jan Karel summed it up pretty well, except the detail regarding sheets(1) containing a sheet level name.

The two situations that are important to me are 1) getting a range reference and 2) deleting a Name object.

1. To get a range object use Range("somename"), you'll either get the sheet level name from the activesheet, or else the book level name. What's on Sheets(1) doesn't matter. If you stick in a sheet qualifier like this Range("shtname!somename"), you'll get that exact range if it exists, otherwise a book range. Sheets(1) still doesn't matter.

2. To delete a book level name. My testing says this simply can not be done if Sheets(1) contains a sheet level name. Once Sheets(1) is clear, you also have to not be on any sheet with a sheet level name. Only then you can use ActiveWorkbook.Names("somename").Delete to delete a book level name object. I don't think there's any other way from VBA to delete the Name.

What a pain in the you know what,

Brian




"Dave Peterson" wrote in message ...
In a private reply:

If you were looking for the range that global name referred to, I thought that
this suggestion worked ok. Did it not work for you?

Option Explicit
Sub testme02b()
Dim myName As Name
Dim myStr As String
Dim myRng As Range

myStr = "somename"

Set myRng = Nothing
For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) = LCase(myStr) Then
'use this one!
Set myRng = myName.RefersToRange
Exit For
End If
Next myName

If myRng Is Nothing Then
Debug.Print "No Global name: " & myStr
Else
Debug.Print myRng.Address(external:=True)
End If

End Sub

Brian Murphy wrote:

I usually need a range object for what the Name refers to. Although sometimes I do need to delete the workbook level Name object.

It was pretty bad when a macro of mine would go to delete a fully qualified book level name, only to find out later that it was a sheet level name that got deleted.

Brian

"Dave Peterson" wrote in message ...
If you were only retrieving the value for a few names, didn't going through the
workbook names collection work for you?


Brian Murphy wrote:

Here's what I've arrived at using the "famous" workaround mentioned by Bob Phillips. It goes for deleting and undeleting just a Name instead of a Worksheet. Could use some error checking to make it more robust. I don't think Hidden sheets will cause problems. Protected sheets might be another matter. Seems to do the job in limited testing so far.

It's a far cry from just a simple syntax fix I was hoping for. I wonder how Jan Karel handled this.

Brian

Option Compare Text

Function myWorkbook_Names(thename$) As Variant
'created this routine to work around "first sheet" problem 5/7/2004
Dim nme$, refersto$
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
With ActiveWorkbook.Names(thename)
nme = .Name
refersto = .refersto
.Delete
End With
'now ActiveWorkbook.Names(thename) should return the correct book level name
Set myWorkbook_Names = ActiveWorkbook.Names(thename)
'put back the deleted sheet level name
ActiveWorkbook.Names.Add Name:=nme, refersto:=refersto
End If
End Function

Function IsNameDefinedAsBookLevel(thename$) As Boolean
Dim o
For Each o In ActiveWorkbook.Names
If o.Name = thename Then
IsNameDefinedAsBookLevel = True
Exit For
End If
Next
End Function

"Dave Peterson" wrote in message ...
Ahhh. I get it and remember a post from Jan Karel Pieterse (IIRC) about the
same problem.

I think his solution was to look for a worksheet level name first. If he found
one, the move to a different sheet.

But maybe you could just add a dummy worksheet as the first one. Delete it when
you're done.

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim testRng As Range
Dim dummyWks As Worksheet

Set dummyWks = Worksheets.Add(befo=Worksheets(1))

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = dummyWks.Name Then
'do nothing
Else
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 "-------------"
End If

Next wks

Application.DisplayAlerts = False
dummyWks.Delete
Application.DisplayAlerts = True

End Sub

Befo
wkbk level from: sheetlevel1 refers to: sheetlevel1
Sheet level also in: [book1]sheetlevel1!$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to: sheetlevel1
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to: sheetlevel1
Sheet level also in: [book1]sheetlevel2!$D$5:$H$15
-------------


After:

wkbk level from: sheetlevel1 refers to: Wkbk_level
Sheet level also in: [book1]sheetlevel1!$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to: Wkbk_level
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to: Wkbk_level
Sheet level also in: [book1]sheetlevel2!$D$5:$H$15
-------------

==============

I think Jan Karel (et al) have to be much more careful. The workbook's
structure could be protected and that would eliminate the ability to add a
sheet.

This might not be a factor for you.


=====
And depending on what you're doing, maybe you can go through the names
collection:

Option Explicit
Sub testme02b()
Dim myName As Name
Dim myStr As String
Dim myRng As Range

myStr = "somename"

Set myRng = Nothing
For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) = LCase(myStr) Then
'use this one!
Set myRng = myName.RefersToRange
Exit For
End If
Next myName

If myRng Is Nothing Then
Debug.Print "No Global name: " & myStr
Else
Debug.Print myRng.Address(external:=True)
End If

End Sub

This consistently gave me:
[book1]Wkbk_level!$E$8:$I$20

No matter the order of the sheets.

(Yeah, I missed that bottom part in your original post. Sorry.)







Brian Murphy wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I sent that last posting too quickly. I said my macro produces a valid book level Name object, but sometimes that's just not possible without shuffling sheets as explained later in the post.

Brian



"Dave Peterson" wrote in message ...
In a private reply:

If you were looking for the range that global name referred to, I thought that
this suggestion worked ok. Did it not work for you?

Option Explicit
Sub testme02b()
Dim myName As Name
Dim myStr As String
Dim myRng As Range

myStr = "somename"

Set myRng = Nothing
For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) = LCase(myStr) Then
'use this one!
Set myRng = myName.RefersToRange
Exit For
End If
Next myName

If myRng Is Nothing Then
Debug.Print "No Global name: " & myStr
Else
Debug.Print myRng.Address(external:=True)
End If

End Sub

Brian Murphy wrote:

I usually need a range object for what the Name refers to. Although sometimes I do need to delete the workbook level Name object.

It was pretty bad when a macro of mine would go to delete a fully qualified book level name, only to find out later that it was a sheet level name that got deleted.

Brian

"Dave Peterson" wrote in message ...
If you were only retrieving the value for a few names, didn't going through the
workbook names collection work for you?


Brian Murphy wrote:

Here's what I've arrived at using the "famous" workaround mentioned by Bob Phillips. It goes for deleting and undeleting just a Name instead of a Worksheet. Could use some error checking to make it more robust. I don't think Hidden sheets will cause problems. Protected sheets might be another matter. Seems to do the job in limited testing so far.

It's a far cry from just a simple syntax fix I was hoping for. I wonder how Jan Karel handled this.

Brian

Option Compare Text

Function myWorkbook_Names(thename$) As Variant
'created this routine to work around "first sheet" problem 5/7/2004
Dim nme$, refersto$
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
With ActiveWorkbook.Names(thename)
nme = .Name
refersto = .refersto
.Delete
End With
'now ActiveWorkbook.Names(thename) should return the correct book level name
Set myWorkbook_Names = ActiveWorkbook.Names(thename)
'put back the deleted sheet level name
ActiveWorkbook.Names.Add Name:=nme, refersto:=refersto
End If
End Function

Function IsNameDefinedAsBookLevel(thename$) As Boolean
Dim o
For Each o In ActiveWorkbook.Names
If o.Name = thename Then
IsNameDefinedAsBookLevel = True
Exit For
End If
Next
End Function

"Dave Peterson" wrote in message ...
Ahhh. I get it and remember a post from Jan Karel Pieterse (IIRC) about the
same problem.

I think his solution was to look for a worksheet level name first. If he found
one, the move to a different sheet.

But maybe you could just add a dummy worksheet as the first one. Delete it when
you're done.

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim testRng As Range
Dim dummyWks As Worksheet

Set dummyWks = Worksheets.Add(befo=Worksheets(1))

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = dummyWks.Name Then
'do nothing
Else
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 "-------------"
End If

Next wks

Application.DisplayAlerts = False
dummyWks.Delete
Application.DisplayAlerts = True

End Sub

Befo
wkbk level from: sheetlevel1 refers to: sheetlevel1
Sheet level also in: [book1]sheetlevel1!$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to: sheetlevel1
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to: sheetlevel1
Sheet level also in: [book1]sheetlevel2!$D$5:$H$15
-------------


After:

wkbk level from: sheetlevel1 refers to: Wkbk_level
Sheet level also in: [book1]sheetlevel1!$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to: Wkbk_level
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to: Wkbk_level
Sheet level also in: [book1]sheetlevel2!$D$5:$H$15
-------------

==============

I think Jan Karel (et al) have to be much more careful. The workbook's
structure could be protected and that would eliminate the ability to add a
sheet.

This might not be a factor for you.


=====
And depending on what you're doing, maybe you can go through the names
collection:

Option Explicit
Sub testme02b()
Dim myName As Name
Dim myStr As String
Dim myRng As Range

myStr = "somename"

Set myRng = Nothing
For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) = LCase(myStr) Then
'use this one!
Set myRng = myName.RefersToRange
Exit For
End If
Next myName

If myRng Is Nothing Then
Debug.Print "No Global name: " & myStr
Else
Debug.Print myRng.Address(external:=True)
End If

End Sub

This consistently gave me:
[book1]Wkbk_level!$E$8:$I$20

No matter the order of the sheets.

(Yeah, I missed that bottom part in your original post. Sorry.)







Brian Murphy wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Ahh. I thought Brian just wanted the reference to the range--not to delete the
name. (I only missed that a handful of times!)

And I (now) see the problem when deleting that name.

Maybe going to that worksheet, deleting the name and coming back would be worth
the effort.

Option Explicit
Sub testme02C()

Dim myName As Name
Dim myStr As String
Dim myRngAddr As String
Dim curSelAddr As String
Dim ActCellAddr As String

myStr = "somename"

application.screenupdating = false

For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) = LCase(myStr) Then
myRngAddr = myName.RefersToRange.Address(external:=True)
curSelAddr = Selection.Address(external:=True)
ActCellAddr = ActiveCell.Address(external:=True)
Application.Goto Range(myRngAddr)
myName.Delete
Application.Goto Range(curSelAddr)
Range(ActCellAddr).Activate
Exit For
End If
Next myName

Application.screenupdating = true

End Sub

It seems like a lot to go through, but it worked (twice in a row for me <bg).

Peter T wrote:

Dave,

First thought when I looked at your Testme02b() was that
it was pretty much the same as my DelBookName(). Only
difference, yours to reference the named range, mine to
delete the name.

I was surprised when Brian came back and said mine didn't
work. But he was right, in the particular scenario as
outlined by Jan Karel.

With similar Sheet & Book level names, referring to
different ranges, on the active sheet, try the
extra "delete" line in your sub:

'code
Set myRng = myName.RefersToRange
myName.Delete ' new line
Exit For
'code

Your "myRng" is correctly referenced to the book level
range, but the wrong name is deleted (ie the sheet level
name). I think deleting the Book level name was one of
Brian's objectives.

Regards,
Peter

-----Original Message-----
In a private reply:

If you were looking for the range that global name
referred to, I thought that
this suggestion worked ok. Did it not work for you?

Option Explicit
Sub testme02b()
Dim myName As Name
Dim myStr As String
Dim myRng As Range

myStr = "somename"

Set myRng = Nothing
For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) = LCase(myStr) Then
'use this one!
Set myRng = myName.RefersToRange
Exit For
End If
Next myName

If myRng Is Nothing Then
Debug.Print "No Global name: " & myStr
Else
Debug.Print myRng.Address(external:=True)
End If

End Sub

Brian Murphy wrote:

I usually need a range object for what the Name refers
to. Although sometimes I do need to delete the workbook
level Name object.

It was pretty bad when a macro of mine would go to
delete a fully qualified book level name, only to find out
later that it was a sheet level name that got deleted.

Brian

"Dave Peterson" wrote in message
...
If you were only retrieving the value for a few
names, didn't going through the
workbook names collection work for you?


Brian Murphy wrote:

Here's what I've arrived at using the "famous"
workaround mentioned by Bob Phillips. It goes for
deleting and undeleting just a Name instead of a
Worksheet. Could use some error checking to make it more
robust. I don't think Hidden sheets will cause problems.
Protected sheets might be another matter. Seems to do the
job in limited testing so far.

It's a far cry from just a simple syntax fix I was
hoping for. I wonder how Jan Karel handled this.

Brian

Option Compare Text

Function myWorkbook_Names(thename$) As Variant
'created this routine to work around "first sheet"
problem 5/7/2004
Dim nme$, refersto$
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
With ActiveWorkbook.Names(thename)
nme = .Name
refersto = .refersto
.Delete
End With
'now ActiveWorkbook.Names(thename) should
return the correct book level name
Set myWorkbook_Names = ActiveWorkbook.Names
(thename)
'put back the deleted sheet level name
ActiveWorkbook.Names.Add Name:=nme,
refersto:=refersto
End If
End Function

Function IsNameDefinedAsBookLevel(thename$) As
Boolean
Dim o
For Each o In ActiveWorkbook.Names
If o.Name = thename Then
IsNameDefinedAsBookLevel = True
Exit For
End If
Next
End Function

"Dave Peterson" wrote in message
...
Ahhh. I get it and remember a post from Jan
Karel Pieterse (IIRC) about the
same problem.

I think his solution was to look for a worksheet
level name first. If he found
one, the move to a different sheet.

But maybe you could just add a dummy worksheet as
the first one. Delete it when
you're done.

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim testRng As Range
Dim dummyWks As Worksheet

Set dummyWks = Worksheets.Add
(befo=Worksheets(1))

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = dummyWks.Name Then
'do nothing
Else
wks.Activate
Debug.Print "wkbk level from: " &
wks.Name & " refers to: " & _

ActiveWorkbook.Names
("somename").RefersToRange.Parent.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 "-------------"
End If

Next wks

Application.DisplayAlerts = False
dummyWks.Delete
Application.DisplayAlerts = True

End Sub

Befo
wkbk level from: sheetlevel1 refers to:
sheetlevel1
Sheet level also in: [book1]sheetlevel1!$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to:
sheetlevel1
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to:
sheetlevel1
Sheet level also in: [book1]sheetlevel2!$D$5:$H$15
-------------


After:

wkbk level from: sheetlevel1 refers to:
Wkbk_level
Sheet level also in: [book1]sheetlevel1!$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to: Wkbk_level
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to:
Wkbk_level
Sheet level also in: [book1]sheetlevel2!$D$5:$H$15
-------------

==============

I think Jan Karel (et al) have to be much more
careful. The workbook's
structure could be protected and that would
eliminate the ability to add a
sheet.

This might not be a factor for you.


=====
And depending on what you're doing, maybe you can
go through the names
collection:

Option Explicit
Sub testme02b()
Dim myName As Name
Dim myStr As String
Dim myRng As Range

myStr = "somename"

Set myRng = Nothing
For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) = LCase(myStr) Then
'use this one!
Set myRng = myName.RefersToRange
Exit For
End If
Next myName

If myRng Is Nothing Then
Debug.Print "No Global name: " & myStr
Else
Debug.Print myRng.Address(external:=True)
End If

End Sub

This consistently gave me:
[book1]Wkbk_level!$E$8:$I$20

No matter the order of the sheets.

(Yeah, I missed that bottom part in your original
post. Sorry.)







Brian Murphy wrote:

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.Parent.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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

.


--

Dave Peterson

Dave,

You're not going to like this, but I don't think it solves
the problem.

Sub MakeSomename()
Dim wk As Worksheet, sName As String
sName = "somename"

For Each nm In ActiveWorkbook.Names
If nm.Name Like "*" & sName Then nm.Delete
Next

ActiveWorkbook.Names.Add Name:=sName, _
RefersToR1C1:="=Sheet1!R7C2"
For Each wk In ActiveWorkbook.Worksheets
i = i + 1
wk.Names.Add Name:=sName, _
RefersToR1C1:="=R1C" & i
Next

For Each nm In ActiveWorkbook.Names
Debug.Print nm.Name, nm
Next
Debug.Print
End Sub

Try MakeSomename, then your testme02C(), then check what
names are left.

Seems the only way is to find and activate a sheet that
does not have Sheet name "somename". If no such sheet
exists, add and activate a new sheet.

I tried building an array of "samename" refersto strings
and relative sheetnames, deleting all "samename", then
rebuilding the sheet level names. But I think that
approach is fraught with problems. I also suspect the new
sheet method might give problems in certain situations.

Don't think there is any problem to reliably reference the
correct book name, only to delete.

Jan Karel suggested the method, then went on to hint - try
it, stumble on unforeseen problems, make a ton of code,
give up, resort to NameManager.xla !

Regards,
Peter

PS For googlers, part of this thread is under the shorter
subject line:
"trouble returning a workbook level Name object "


-----Original Message-----
Ahh. I thought Brian just wanted the reference to the
range--not to delete the
name. (I only missed that a handful of times!)

And I (now) see the problem when deleting that name.

Maybe going to that worksheet, deleting the name and
coming back would be worth
the effort.

Option Explicit
Sub testme02C()

Dim myName As Name
Dim myStr As String
Dim myRngAddr As String
Dim curSelAddr As String
Dim ActCellAddr As String

myStr = "somename"

application.screenupdating = false

For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) = LCase(myStr)
Then
myRngAddr = myName.RefersToRange.Address
(external:=True)
curSelAddr = Selection.Address(external:=True)
ActCellAddr = ActiveCell.Address
(external:=True)
Application.Goto Range(myRngAddr)
myName.Delete
Application.Goto Range(curSelAddr)
Range(ActCellAddr).Activate
Exit For
End If
Next myName

Application.screenupdating = true

End Sub

It seems like a lot to go through, but it worked (twice
in a row for me <bg).

Peter T wrote:

Dave,

First thought when I looked at your Testme02b() was that
it was pretty much the same as my DelBookName(). Only
difference, yours to reference the named range, mine to
delete the name.

I was surprised when Brian came back and said mine
didn't
work. But he was right, in the particular scenario as
outlined by Jan Karel.

With similar Sheet & Book level names, referring to
different ranges, on the active sheet, try the
extra "delete" line in your sub:

'code
Set myRng = myName.RefersToRange
myName.Delete ' new line
Exit For
'code

Your "myRng" is correctly referenced to the book level
range, but the wrong name is deleted (ie the sheet level
name). I think deleting the Book level name was one of
Brian's objectives.

Regards,
Peter

-----Original Message-----
In a private reply:

If you were looking for the range that global name
referred to, I thought that
this suggestion worked ok. Did it not work for you?

Option Explicit
Sub testme02b()
Dim myName As Name
Dim myStr As String
Dim myRng As Range

myStr = "somename"

Set myRng = Nothing
For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) = LCase(myStr) Then
'use this one!
Set myRng = myName.RefersToRange
Exit For
End If
Next myName

If myRng Is Nothing Then
Debug.Print "No Global name: " & myStr
Else
Debug.Print myRng.Address(external:=True)
End If

End Sub

Brian Murphy wrote:

I usually need a range object for what the Name
refers
to. Although sometimes I do need to delete the workbook
level Name object.

It was pretty bad when a macro of mine would go to
delete a fully qualified book level name, only to find
out
later that it was a sheet level name that got deleted.

Brian

"Dave Peterson" wrote in message
...
If you were only retrieving the value for a few
names, didn't going through the
workbook names collection work for you?


Brian Murphy wrote:

Here's what I've arrived at using the "famous"
workaround mentioned by Bob Phillips. It goes for
deleting and undeleting just a Name instead of a
Worksheet. Could use some error checking to make it
more
robust. I don't think Hidden sheets will cause
problems.
Protected sheets might be another matter. Seems to do
the
job in limited testing so far.

It's a far cry from just a simple syntax fix I
was
hoping for. I wonder how Jan Karel handled this.

Brian

Option Compare Text

Function myWorkbook_Names(thename$) As Variant
'created this routine to work around "first
sheet"
problem 5/7/2004
Dim nme$, refersto$
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
With ActiveWorkbook.Names(thename)
nme = .Name
refersto = .refersto
.Delete
End With
'now ActiveWorkbook.Names(thename) should
return the correct book level name
Set myWorkbook_Names =
ActiveWorkbook.Names
(thename)
'put back the deleted sheet level name
ActiveWorkbook.Names.Add Name:=nme,
refersto:=refersto
End If
End Function

Function IsNameDefinedAsBookLevel(thename$) As
Boolean
Dim o
For Each o In ActiveWorkbook.Names
If o.Name = thename Then
IsNameDefinedAsBookLevel = True
Exit For
End If
Next
End Function

"Dave Peterson" wrote in
message
...
Ahhh. I get it and remember a post from Jan
Karel Pieterse (IIRC) about the
same problem.

I think his solution was to look for a
worksheet
level name first. If he found
one, the move to a different sheet.

But maybe you could just add a dummy worksheet
as
the first one. Delete it when
you're done.

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim testRng As Range
Dim dummyWks As Worksheet

Set dummyWks = Worksheets.Add
(befo=Worksheets(1))

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = dummyWks.Name Then
'do nothing
Else
wks.Activate
Debug.Print "wkbk level from: " &
wks.Name & " refers to: " & _

ActiveWorkbook.Names
("somename").RefersToRange.Parent.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 "-------------"
End If

Next wks

Application.DisplayAlerts = False
dummyWks.Delete
Application.DisplayAlerts = True

End Sub

Befo
wkbk level from: sheetlevel1 refers to:
sheetlevel1
Sheet level also in: [book1]sheetlevel1!
$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to:
sheetlevel1
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to:
sheetlevel1
Sheet level also in: [book1]sheetlevel2!
$D$5:$H$15
-------------


After:

wkbk level from: sheetlevel1 refers to:
Wkbk_level
Sheet level also in: [book1]sheetlevel1!
$D$5:$H$15
-------------
wkbk level from: Wkbk_level refers to:
Wkbk_level
Not a sheet level name in: Wkbk_level
-------------
wkbk level from: sheetlevel2 refers to:
Wkbk_level
Sheet level also in: [book1]sheetlevel2!
$D$5:$H$15
-------------

==============

I think Jan Karel (et al) have to be much more
careful. The workbook's
structure could be protected and that would
eliminate the ability to add a
sheet.

This might not be a factor for you.


=====
And depending on what you're doing, maybe you
can
go through the names
collection:

Option Explicit
Sub testme02b()
Dim myName As Name
Dim myStr As String
Dim myRng As Range

myStr = "somename"

Set myRng = Nothing
For Each myName In ActiveWorkbook.Names
If LCase(myName.Name) = LCase(myStr)
Then
'use this one!
Set myRng = myName.RefersToRange
Exit For
End If
Next myName

If myRng Is Nothing Then
Debug.Print "No Global name: " & myStr
Else
Debug.Print myRng.Address
(external:=True)
End If

End Sub

This consistently gave me:
[book1]Wkbk_level!$E$8:$I$20

No matter the order of the sheets.

(Yeah, I missed that bottom part in your
original
post. Sorry.)







Brian Murphy wrote:

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.Parent.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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

.


--

Dave Peterson

.