View a Tabbed List of All Worksheet Names Simultaneously

T

Takeadoe

Dear NG - My workbook has grown so large with worksheets that I find
myself spending a bunch of time scrolling through the list of
worksheet names looking for the sheet I'm after. It would be really
handy if there was a way (and I think there is) to view all of the
worksheet names at one time in a tabbed list that would appear "on top
of" your worksheet. I recall seeing something like this some time
back and I wish that I would have made better note of it. If anyone
can help, I sure would appreciate it.

Thanks in Advance.

Mike
 
H

Harlan Grove

Takeadoe said:
Dear NG - My workbook has grown so large with worksheets that I find
myself spending a bunch of time scrolling through the list of
worksheet names looking for the sheet I'm after. It would be really
handy if there was a way (and I think there is) to view all of the
worksheet names at one time in a tabbed list that would appear "on
top of" your worksheet. I recall seeing something like this some
time back and I wish that I would have made better note of it. If
anyone can help, I sure would appreciate it.
Click to expand...

At some point the number of worksheets could exceed screen capacity,
so you need to accomodate scrolling.

If you mean multiple rows of tabs, one for each worksheet, that's
NEVER been a feature in ANY Excel version. If you believe you've seen
it, you didn't see it in Excel, and there's no way to implement it in
Excel.

Approximations do exist. If you right-click on the worksheet
navigation buttons just to the left of the worksheet tabs below the
worksheet window, Excel displays a popup menu of worksheet names with
a bottommost entry of More Sheets... if there are more than can fit in
the popup menu. Clicking on More Sheets... displays a scrolling dialog
showing all worksheet names.

If you want to see as many worksheet names as possible on screen at
once, you'd need a macro to fill a worksheet range with links to each
worksheet. That's been asked and answered before, so search the Google
Groups archives if that's what you want.
 
G

Gord Dibben

Here is macro from Bob Phillips that pops up a list of sheets to choose from.

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


Gord Dibben MS Excel MVP
 
J

jan

Mike,

This is a possibility:

Make a UserForm (UserForm1)
Put a Tabstrip (TabStrip1) on it and delete the two tabs from it.
MultRow=Yes

Put this code in the module that belongs to the UserForm

Option Explicit

Private Sub TabStrip1_Change()
Sheets(Me.TabStrip1.SelectedItem.Name).Select
End Sub

Private Sub UserForm_Initialize()
Dim sh As Worksheet
Dim i As Integer
For Each sh In ThisWorkbook.Worksheets
UserForm1.TabStrip1.Tabs.Add sh.Name, sh.Name, i
i = i + 1
Next
End Sub

Then, when opening the userform it will show a tabstrip with as many tabs as
there are worksheets.
Clicking a tab selects the corresponding worksheet.

Jan
 
T

Takeadoe

Mike,

This is a possibility:

Make a UserForm (UserForm1)
Put a Tabstrip (TabStrip1) on it and delete the two tabs from it.
MultRow=Yes

Put this code in the module that belongs to the UserForm

Option Explicit

Private Sub TabStrip1_Change()
Sheets(Me.TabStrip1.SelectedItem.Name).Select
End Sub

Private Sub UserForm_Initialize()
Dim sh As Worksheet
Dim i As Integer
For Each sh In ThisWorkbook.Worksheets
UserForm1.TabStrip1.Tabs.Add sh.Name, sh.Name, i
i = i + 1
Next
End Sub

Then, when opening the userform it will show a tabstrip with as many tabs as
there are worksheets.
Clicking a tab selects the corresponding worksheet.

Jan
Click to expand...

Jan - Thank you for that neat idea!

Mike
 
T

Takeadoe

Here is macro from Bob Phillips that pops up a list of sheets to choose from.

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

Gord Dibben MS Excel MVP





- Show quoted text -
Click to expand...

Gord - Thank you very much for that neat tip. I really appreciate you
taking time from your schedule to help!
Mike
 
T

Takeadoe

At some point the number of worksheets could exceed screen capacity,
so you need to accomodate scrolling.

If you mean multiple rows of tabs, one for each worksheet, that's
NEVER been a feature in ANY Excel version. If you believe you've seen
it, you didn't see it in Excel, and there's no way to implement it in
Excel.

Approximations do exist. If you right-click on the worksheet
navigation buttons just to the left of the worksheet tabs below the
worksheet window, Excel displays a popup menu of worksheet names with
a bottommost entry of More Sheets... if there are more than can fit in
the popup menu. Clicking on More Sheets... displays a scrolling dialog
showing all worksheet names.

If you want to see as many worksheet names as possible on screen at
once, you'd need a macro to fill a worksheet range with links to each
worksheet. That's been asked and answered before, so search the Google
Groups archives if that's what you want.
Click to expand...

Harlan,

Your second to last paragraph from the bottom was really what I was
looking for! Thank you very much for taking time to help.

Mike
 
Top