worksheet names

  • Thread starter Thread starter greta
  • Start date Start date
G

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...
 
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.
 
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)
 
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
 
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)
 
Back
Top