Workbook Names

[Paul Smith]

I understand that if I call the workbook.names collection I am calling a
collection of all names that are present in the workbook.

What I want to be able to distinguish between which names are workbook level
ones and which are worksheet level ones!

PWS

 

[Norman Jones]

Hi Paul,

Sub TestNames()
Dim Nme As Name

For Each Nme In ThisWorkbook.Names
If Nme.Name Like "*!*" Then
MsgBox Nme.Name & vbTab & "Sheet Level Name"
Else
MsgBox Nme.Name & vbTab & "Workbook Level name"
End If
Next Nme

End Sub

 

[Norman Jones]

Hi Paul,

To add, if you follow KeepITcool's suggestion (which I enthusiastically
endorse) to download Jan Karel Pieterse's Name Manager addin, you will
immediatly be able to distingish betwween global and local names - and
*much* more.

 

[Bob Phillips]

You have to be careful, as workbook names are hidden from you if you have a
worksheet level name with the same name and that worksheet is active. See
http://www.xldynamic.com/source/xld.Names.html