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
.