Hi,
Thanks for reading this. How would you add a 2nd or third row of sheet tabs
to the bottom of a Excel 2003 worksheet. It would be much faster to click a
tab on a 2nd or 3rd row than using <CRTL <Page Down to work your way across
the choices. If a freeware solution isn't available, I'm ok paying. I've
looked at some of the choices on the web for addins. I've also looked at MS
database. So far, no luck.
Thanks, Norman Hawes

Don't bother looking any further.

There is no way to get more than one row of sheet tabs.

If you right-click on one of the navigation arrows left of the sheet tabs, you
will get a pop-up menu of 16 sheets to choose from and "more sheets" if needed.

If you you have many sheets you can use a macro to pop up a table of contents
form, from which you can select a sheet.

I like Bob Phillips' code for this.

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


On Mon, 26 Jun 2006 11:41:02 -0700, normanhawes
wrote:

Hi,
Thanks for reading this. How would you add a 2nd or third row of sheet tabs
to the bottom of a Excel 2003 worksheet. It would be much faster to click a
tab on a 2nd or 3rd row than using <CRTL <Page Down to work your way across
the choices. If a freeware solution isn't available, I'm ok paying. I've
looked at some of the choices on the web for addins. I've also looked at MS
database. So far, no luck.
Thanks, Norman Hawes



Gord Dibben MS Excel MVP

Not exactly what you're asking for, but if you right click on the arrows to
the right of the sheet tab names, a listing of all sheets in the current
workbook is displayed and you can choose the sheet you want.

dordale :)

"normanhawes" wrote in message
...
Hi,
Thanks for reading this. How would you add a 2nd or third row of sheet
tabs
to the bottom of a Excel 2003 worksheet. It would be much faster to click
a
tab on a 2nd or 3rd row than using <CRTL <Page Down to work your way
across
the choices. If a freeware solution isn't available, I'm ok paying. I've
looked at some of the choices on the web for addins. I've also looked at
MS
database. So far, no luck.
Thanks, Norman Hawes

Thanks for the quick response. Worked like a champ. Thanks again. Norman
Hawes

"Gord Dibben" wrote:

Don't bother looking any further.

There is no way to get more than one row of sheet tabs.

If you right-click on one of the navigation arrows left of the sheet tabs, you
will get a pop-up menu of 16 sheets to choose from and "more sheets" if needed.

If you you have many sheets you can use a macro to pop up a table of contents
form, from which you can select a sheet.

I like Bob Phillips' code for this.

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


On Mon, 26 Jun 2006 11:41:02 -0700, normanhawes
wrote:

Hi,
Thanks for reading this. How would you add a 2nd or third row of sheet tabs
to the bottom of a Excel 2003 worksheet. It would be much faster to click a
tab on a 2nd or 3rd row than using <CRTL <Page Down to work your way across
the choices. If a freeware solution isn't available, I'm ok paying. I've
looked at some of the choices on the web for addins. I've also looked at MS
database. So far, no luck.
Thanks, Norman Hawes



Gord Dibben MS Excel MVP

Thanks for the quick response. I used Gord Dibben idea. Worked pretty well.
Thanks again. Norman Hawes

"dordale" wrote:

Not exactly what you're asking for, but if you right click on the arrows to
the right of the sheet tab names, a listing of all sheets in the current
workbook is displayed and you can choose the sheet you want.

dordale :)

"normanhawes" wrote in message
...
Hi,
Thanks for reading this. How would you add a 2nd or third row of sheet
tabs
to the bottom of a Excel 2003 worksheet. It would be much faster to click
a
tab on a 2nd or 3rd row than using <CRTL <Page Down to work your way
across
the choices. If a freeware solution isn't available, I'm ok paying. I've
looked at some of the choices on the web for addins. I've also looked at
MS
database. So far, no luck.
Thanks, Norman Hawes

Thanks to Bob for the code.

Appreciate the feedback.

Gord

On Tue, 27 Jun 2006 20:37:01 -0700, normanhawes
wrote:

Thanks for the quick response. Worked like a champ. Thanks again. Norman
Hawes

"Gord Dibben" wrote:

Don't bother looking any further.

There is no way to get more than one row of sheet tabs.

If you right-click on one of the navigation arrows left of the sheet tabs, you
will get a pop-up menu of 16 sheets to choose from and "more sheets" if needed.

If you you have many sheets you can use a macro to pop up a table of contents
form, from which you can select a sheet.

I like Bob Phillips' code for this.

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


On Mon, 26 Jun 2006 11:41:02 -0700, normanhawes
wrote:

Hi,
Thanks for reading this. How would you add a 2nd or third row of sheet tabs
to the bottom of a Excel 2003 worksheet. It would be much faster to click a
tab on a 2nd or 3rd row than using <CRTL <Page Down to work your way across
the choices. If a freeware solution isn't available, I'm ok paying. I've
looked at some of the choices on the web for addins. I've also looked at MS
database. So far, no luck.
Thanks, Norman Hawes



Gord Dibben MS Excel MVP


Gord Dibben MS Excel MVP