How do I create a first page index of worksheets in my workbook?
 

Hi!

I've a workbook with over 40 worksheets, and would like to create a
list(index) on the first worksheet, so that (a) any user can see all the
worksheets in the workbook, and (b) perhaps if I click on the worksheet name,
I can go to that worksheet?

How do I create a first page index of worksheets in my workbook?
 

Perhaps the easiest way is to create Hyperlinks to cell A1 of each sheet.

From the Excel main menu:
<insert<hyperlink
Click the [Place in this document] button
Select the sheet (in the Cell Reference structure)
Change the text to display.
Click [OK]

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Sam" wrote:

Hi!

I've a workbook with over 40 worksheets, and would like to create a
list(index) on the first worksheet, so that (a) any user can see all the
worksheets in the workbook, and (b) perhaps if I click on the worksheet name,
I can go to that worksheet?

How do I create a first page index of worksheets in my workbook?
 

There is vba code here, by David McRitchie, if you want to do it yourself...
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
-0r-
you could find the XL Extras add-in at my website and give it a try...
http://www.realezsites.com/bus/primitivesoftware
(it also sorts sheets and does other stuff)
--
Jim Cone
San Francisco, USA



"Sam"
wrote in message
Hi!
I've a workbook with over 40 worksheets, and would like to create a
list(index) on the first worksheet, so that (a) any user can see all the
worksheets in the workbook, and (b) perhaps if I click on the worksheet name,
I can go to that worksheet?

How do I create a first page index of worksheets in my workbook?
 

Off topic...

I've been meaning to tell you this for a while...

You have one of the best designed websites I've ever seen. No clutter, no
junk, no gaudy graphics, no BS.

Excellent job!

--
Biff
Microsoft Excel MVP


"Jim Cone" wrote in message
...

There is vba code here, by David McRitchie, if you want to do it
yourself...
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
-0r-
you could find the XL Extras add-in at my website and give it a try...
http://www.realezsites.com/bus/primitivesoftware
(it also sorts sheets and does other stuff)
--
Jim Cone
San Francisco, USA



"Sam"
wrote in message
Hi!
I've a workbook with over 40 worksheets, and would like to create a
list(index) on the first worksheet, so that (a) any user can see all the
worksheets in the workbook, and (b) perhaps if I click on the worksheet
name,
I can go to that worksheet?

How do I create a first page index of worksheets in my workbook?
 

Biff,
Thank you for the kind words.
I have to share credit with Chuck Roberts "CLR" who was a big help
to me in getting the thing put together.

Also, congratulations to you for your well deserved recognition.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"T. Valko"
wrote in message
Off topic...
I've been meaning to tell you this for a while...
You have one of the best designed websites I've ever seen. No clutter, no
junk, no gaudy graphics, no BS.
Excellent job!
--
Biff
Microsoft Excel MVP



"Jim Cone" wrote in message

There is vba code here, by David McRitchie, if you want to do it
yourself...
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
-0r-
you could find the XL Extras add-in at my website and give it a try...
http://www.realezsites.com/bus/primitivesoftware
(it also sorts sheets and does other stuff)
--
Jim Cone
San Francisco, USA



"Sam"
wrote in message
Hi!
I've a workbook with over 40 worksheets, and would like to create a
list(index) on the first worksheet, so that (a) any user can see all the
worksheets in the workbook, and (b) perhaps if I click on the worksheet
name,
I can go to that worksheet?

How do I create a first page index of worksheets in my workbook?
 

You're very welcome!

--
Biff
Microsoft Excel MVP


"Jim Cone" wrote in message
...

Biff,
Thank you for the kind words.
I have to share credit with Chuck Roberts "CLR" who was a big help
to me in getting the thing put together.

Also, congratulations to you for your well deserved recognition.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"T. Valko"
wrote in message
Off topic...
I've been meaning to tell you this for a while...
You have one of the best designed websites I've ever seen. No clutter, no
junk, no gaudy graphics, no BS.
Excellent job!
--
Biff
Microsoft Excel MVP



"Jim Cone" wrote in message

There is vba code here, by David McRitchie, if you want to do it
yourself...
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
-0r-
you could find the XL Extras add-in at my website and give it a try...
http://www.realezsites.com/bus/primitivesoftware
(it also sorts sheets and does other stuff)
--
Jim Cone
San Francisco, USA



"Sam"
wrote in message
Hi!
I've a workbook with over 40 worksheets, and would like to create a
list(index) on the first worksheet, so that (a) any user can see all the
worksheets in the workbook, and (b) perhaps if I click on the worksheet
name,
I can go to that worksheet?

How do I create a first page index of worksheets in my workbook?
 

Sam

I prefer Bob Phillips' browsesheet code to go to any sheet in the workbook so
you don't have to keep going back to the TOC sheet.

Sub BrowseSheets()
Const nPerColumn As Long = 38 'number of items per column
Const nWidth As Long = 13 'width of each letter
Const nHeight As Long = 18 'height of each row
Const sID As String = "___SheetGoto" 'name of dialog sheet
Const kCaption As String = " Select sheet to goto"
'dialog caption


Dim i As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim cLeft As Long
Dim thisDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As OptionButton
Application.ScreenUpdating = False
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If
On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(sID).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set CurrentSheet = ActiveSheet
Set thisDlg = ActiveWorkbook.DialogSheets.Add
With thisDlg
.Name = sID
.Visible = xlSheetHidden
'sets variables for positioning on dialog
iBooks = 0
cCols = 0
cMaxLetters = 0
cLeft = 78
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
cLeft = cLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
cLetters = Len(CurrentSheet.Name)
If cLetters cMaxLetters Then
cMaxLetters = cLetters
End If
iBooks = iBooks + 1
.OptionButtons.Add cLeft, TopPos, cLetters * nWidth, 16.5
.OptionButtons(iBooks).text = _
ActiveWorkbook.Worksheets(iBooks).Name
TopPos = TopPos + 13
Next i
.Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24
CurrentSheet.Activate
With .DialogFrame
.Height = Application.Max(68, _
Application.Min(iBooks, nPerColumn) * nHeight + 10)
.Width = cLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With
.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront
Application.ScreenUpdating = True
If .Show Then
For Each cb In thisDlg.OptionButtons
If cb.Value = xlOn Then
ActiveWorkbook.Worksheets(cb.Caption).Select
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If
Application.DisplayAlerts = False
.Delete
End With
End Sub

NOTE: Gary Brown revised this code to cover hidden sheets and chart sheets and
other potential problems.

See this thread for that revised code.

http://snipurl.com/1l8o4

I would make one more change to Gary's code.

Const nWidth As Long = 8 'width of each letter
Change to 10 or sheetnames < 4 chars will be incomplete


Gord Dibben MS Excel MVP


On Tue, 3 Jul 2007 18:24:01 -0700, Sam wrote:

Hi!

I've a workbook with over 40 worksheets, and would like to create a
list(index) on the first worksheet, so that (a) any user can see all the
worksheets in the workbook, and (b) perhaps if I click on the worksheet name,
I can go to that worksheet?

How do I create a first page index of worksheets in my workbook?
 

a real easy way to do this would be to use an excel addin called asap
utilities. it can do other neat stuff with just a click or two... its a free
download that has made life easier.

"Sam" wrote:

Hi!

I've a workbook with over 40 worksheets, and would like to create a
list(index) on the first worksheet, so that (a) any user can see all the
worksheets in the workbook, and (b) perhaps if I click on the worksheet name,
I can go to that worksheet?