Great! this does it. Thanks for all the added info too. I'll keep the whole
thing for future use, but I was able to simplify it for my particular
purpose - (the workbooks I need this for, contain only local names and not
much chance I'll need error handling (thankfully at this stage))
PS. I'm using 97 so I couldn't make use of the download you suggested
(2000+) but dank maat anyway.
PQP




"keepitcool" wrote in message
...

In most cases following should work

Sub NamesTOC()
Dim i%, n%, s$
On Error Resume Next
Worksheets("toc").Activate
n = 10
Cells(n, 2).CurrentRegion.Clear
For i = 1 To Names.Count
With Names(i)
'check if it is a range not a formula/constant
s = .RefersToRange.Address
If Err = 0 Then
n = n + 1
Cells.Hyperlinks.Add Cells(n, 2), "", .Name, , .Name
End If
End With
Next
End Sub

The names collection is rather more complex then you'd assume.
This is caused by the fact that it uses several indexes.
(numerical index, usenglish name, locale name, range address)


When you have both "global" and "local" names, or similar names on
several worksheets, you need to know some of it's intricacies.

a global name = it's parent is the workbook
a local name = it's parent is a worksheet.

Officially a local name's name property is like "Sheet1!myRange"
but you can call it (on the same sheet) with it's abbreviated reference
"myRange"

When you start with a sheet which contains "GLOBAL" defined names,
and when you copy that sheet= the copied sheet will contain "LOCAL"
names
and you have an unwanted (confusing) mixture of global and local names.


If a LOCAL name exists on the sheet where you are using it
(either in a macro on the activesheet or in a cell's formula the cells
worksheet) then the abbreviated reference "MyRange" will retrieve the
LOCAL sibling from the Names collection EVEN if a Global sibling exists.


I suggest you check out these addins:
NameIt http://members.chello.nl/keepitcool/downloads
NameEditor http://www.decisionmodels.com/downloads.htm



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"pQp" wrote:

Thanks to all the folk who let us use their hard work for ourselves,
I've found a few great little addins that create a TOC with links for
each page by creating names in A1 of each page. (here's one if anybody
wants something like this
http://www.tushar-mehta.com/excel/so...toccreator.zip)

But after lot of searching, I still can't find a way to do the same
for EVERY named range in a book.
If there's not one about that anyone knows of, could I create my own
by automating the making of links from a sheet of enumerated names
somehow? Or perhaps a click event which passes the name reference to a
goto? ...or sumpthin!

Ta All