Worksheet Tab index

  • Thread starter Thread starter S G Tan
  • Start date Start date
S

S G Tan

I have 15 or more worksheet tabs. How do I make a list/auto-populate of all
the name of the tabs on one worksheet to form the basis of an index?
 
Don has given you code to get a list of sheets.

What do you want to do with this "index"?

You want to be able to select any sheet when you click on a name?

You can build a Sheet Navigation Toolbar as per Dave Peterson's code on
Debra Dalgleish's site. Sorts as well as navigates.

http://www.contextures.on.ca/xlToolbar01.html

Or see this google search thread for the BrowseSheets macro from Bob
Phillips.

http://tinyurl.com/yoa3dw


Gord Dibben MS Excel MVP
 
Many thanks to both of you.

Gord Dibben said:
Don has given you code to get a list of sheets.

What do you want to do with this "index"?

You want to be able to select any sheet when you click on a name?

You can build a Sheet Navigation Toolbar as per Dave Peterson's code on
Debra Dalgleish's site. Sorts as well as navigates.

http://www.contextures.on.ca/xlToolbar01.html

Or see this google search thread for the BrowseSheets macro from Bob
Phillips.

http://tinyurl.com/yoa3dw


Gord Dibben MS Excel MVP
Click to expand...
 
When I do that, it defaults to the top of the page and start the list. Is it
possible to change the macro,so that it list the start of the TOC at the cell
that I specify?
 
Sub toc()
Dim i, c, r As Long
r = ActiveCell.Row - 1
c = ActiveCell.Column
For i = 1 To Sheets.Count
Cells(i + r, c).Value = Sheets(i).Name
Next i
End Sub


Gord Dibben MS Excel MVP
 
thanks again - that worked.

Gord Dibben said:
Sub toc()
Dim i, c, r As Long
r = ActiveCell.Row - 1
c = ActiveCell.Column
For i = 1 To Sheets.Count
Cells(i + r, c).Value = Sheets(i).Name
Next i
End Sub


Gord Dibben MS Excel MVP
Click to expand...
 
Could I trouble you again? Is it possible to produce that index and hyperlink
to the relevant tab?
 
Just use a double click event instead on the name typed into the cell.
Right click sheet tab>view code>insert this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(WantedSheet) Is Nothing Then
'GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(WantedSheet).Range("a4")
End If
Application.DisplayAlerts = True
End Sub
 
Back
Top