Create a TOC for ALL names in workbook?
 

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

Create a TOC for ALL names in workbook?
 

Take a look he

http://www.mcgimpsey.com/excel/listnames.html

There's a link on that page to Jan Karel Pieterse's fabulous add-in
"Name Manger.xla"


In article ,
"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

Create a TOC for ALL names in workbook?
 

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

Create a TOC for ALL names in workbook?
 

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


"J.E. McGimpsey" wrote in message
...
Take a look he

http://www.mcgimpsey.com/excel/listnames.html

There's a link on that page to Jan Karel Pieterse's fabulous add-in
"Name Manger.xla"


In article ,
"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

Create a TOC for ALL names in workbook?
 

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

Create a TOC for ALL names in workbook?
 

It appears that you may have plans of including additional material
from the indexed sheets as well, so ...

You might also take a look at
Build Table of Contents, similar listings, working with Hyperlinks
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
and
Build TOC Another Approach
http://www.mvps.org/dmcritchie/excel/excel.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"pQp" wrote in message ...
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