worksheet names

[greta]

We have workbooks with so very many worksheets in them.
Is there a way to have one sheet that lists all the
worksheets in the workbook?
Kind of like a table of contents?
My boss would love me if I could get this done.
Thanks...

 

[Vasant Nanavati]

If you right-click on the "VCR buttons" (bottom left of your workbook
window), a navigable list of all the worksheets in the workbook will pop up.

 

[Bob Phillips]

Greta,

2 ways for you, but both VBA I am afraid. First is

Sub ListSheet
Dim sh as Worksheet
Dim i As Long

For i = 1 To Worksheets.Count
Cells(i,"A").Value = Worksheets(i).Name
Next i

End Sub

This way is longer, but much neater in my view.

Sub BrowseSheets()
Dim i As Integer
Dim TopPos As Integer
Dim iBooks As Integer
Dim PrintDlg 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

Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add

iBooks = 0

TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
iBooks = iBooks + 1
PrintDlg.OptionButtons.Add 78, TopPos, 150, 16.5
PrintDlg.OptionButtons(iBooks).Text = _
ActiveWorkbook.Worksheets(iBooks).Name
TopPos = TopPos + 13
Next i

PrintDlg.Buttons.Left = 240

CurrentSheet.Activate

With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select workbooks to process"
End With

PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront

Application.ScreenUpdating = True
If PrintDlg.Show Then
For Each cb In PrintDlg.OptionButtons
If cb.Value = xlOn Then
'ActiveWorkbook.Worksheets(cb.Caption).Select
MsgBox "Worksheet " & Worksheets(cb.Caption).Name & "
selected"
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If

Application.DisplayAlerts = False
PrintDlg.Delete

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Gord Dibben]

Bob

Like the second version with the selection dialog form, but the line

'ActiveWorkbook.Worksheets(cb.Caption).Select has a comment mark(') which
should not be there. Prevents user from switching to that sheet when option
button selected.

Typo? Or my newsreader plunked it in?

Gord

 

[JE McGimpsey]

Take a look at David McRitchie's BuildTOC macro:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm


which creates a Table of Contents with hyperlinks to the sheets.

 

[Bob Phillips]

Gord,

It's neat isn't it. It is actually adapted (marginally) from a technique on
John Walkenbach's site.

That line is from my original code which uses this technique to select/goto
a worksheet. I commented that line out because the OP only wanted a list,
but I thought she might want to do more when she has the list, hence this
suggestion. I added a MsgBox to show which sheet was selected.

So, it was deliberate<G>.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Gord Dibben]

So, it was deliberate<G>.

Ahhhh so!

Thanks, Gord