UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Hyperlink Table of Contents Worksheet    
 
   
NateO
post Oct 2 2003, 12:35 PM
Post#1


Remembered
Posts: 5,055
Joined: 27-March 03
From: Minneapolis, MN, USA


Hello, if you're interested in having code generate a worksheet that acts a Table of Contents for a workbook's worksheets please see the following:
pre>Sub Sheet_TOC()
[color="#00007F"]Dim ws [color="#00007F"]As Worksheet, wsNw [color="#00007F"]As Worksheet, n [color="#00007F"]As [color="#00007F"]Integer
[color="#00007F"]Set wsNw = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Sheets(1))
[color="#00007F"]With wsNw
[color="#00007F"]On [color="#00007F"]Error [color="#00007F"]GoTo 2
1: .Name = "Workbook Index"
[color="#00007F"]On [color="#00007F"]Error [color="#00007F"]GoTo 0
.[a1] = ThisWorkbook.Name & "[color="#007F00"]'s Worksheets:"
.[a1].Font.Size = 15
n = 3
[color="#00007F"]For [color="#00007F"]Each ws [color="#00007F"]In ThisWorkbook.Worksheets
[color="#00007F"]If ws.Name <> .Name [color="#00007F"]Then
.Cells(n, 1) = ws.Name
.Hyperlinks.Add _
Anchor:=.Cells(n, 1), _
Address:="", _
SubAddress:="[color="#007F00"]'" & ws.Name & "'!A1"
[color="#00007F"]With ws
.[a1] = Sheets(1).Name
.[a1].Hyperlinks.Add _
Anchor:=.Cells(1, 1), _
Address:="", _
SubAddress:="[color="#007F00"]'" & Sheets(1).Name & "'!A1"
[color="#00007F"]End [color="#00007F"]With
n = n + 1
[color="#00007F"]End [color="#00007F"]If
[color="#00007F"]Next
[color="#00007F"]End [color="#00007F"]With
[color="#00007F"]Exit [color="#00007F"]Sub
2: Application.DisplayAlerts = [color="#00007F"]False
Sheets("Workbook Index").Delete
Application.DisplayAlerts = [color="#00007F"]True
[color="#00007F"]GoTo 1
[color="#00007F"]End [color="#00007F"]Sub

This code is dynamic and has been tested in '97-XP, where some of the hyperlink arguments had changed. The code will generate a link for each sheet, and insert a link in a1 of each sheet that links back the Table of Contents page. Also, it will overwrite the TOC error-free, so if you add another sheet, simply rerun the code.
Possibly of interest. Questions/concerns should be addressed up in the Excel forum.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 05:20 PM