Create a TOC for ALL names in workbook?

  • Thread starter Thread starter pQp
  • Start date Start date
P

pQp

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/software/maketoc/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
 
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 >
 
HI J,
Thanks, but this is what I meant, I have utils to create LINKED tocs, and to
list names, (including this excellent one already), but I'm after something
that'll combine both ideas - ie: list ALL the names with links, rather than
just the sheet names, to make a more comprehensive linked TOC.... perhaps by
using the sheet that a name lister creates.
(tho' can't hurt to have the link for NameManager posted again, it's well
worth having)
PQP
 
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
 
Back
Top