Tab Selection

[MickkyB]

I'm using Excel 2003 and wondered if there Is a quick way of selecting a tab where at least 40 tabs exist in a workbook.
i appreciate that i can select the scroll arrows but this shows a pop up whereby i have to select more sheets and then i have to scroll through the lsit to select the tab i require.
Is there a way to show all tabs in one window or some other quick option?

Thanks in advance
Mick


--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-

 

[Franz Erhart]

Yes, there ist, but not out of the box.
1) Add a new sheet to hold a list of all tabs, name it 'Inhalt' or whatever
you like
2) Add all the tab names to this list and create a hyper link to the
respective tabs - the following macro is doing just that:



Sub InhaltsverzeichnisErstellen()
Dim i As Integer
Dim j As Integer

'ActiveWorkbook.Sheets.Add Before:=Worksheets(1)
'ActiveSheet.Name = "Inhalt"
Sheets("Inhalt").Select
Cells.Select
Selection.Clear

Range("A1").Value = "Inhaltsverzeichnis"
ActiveCell.Offset(2, 0).Select
For i = 2 To ActiveWorkbook.Sheets.Count
ActiveCell.Value = i - 1
If (Sheets(i).Type = xlWorksheet) Then
ActiveCell.Offset(0, 2).Value = True
Else
ActiveCell.Offset(0, 2).Value = False
End If
ActiveCell.Offset(0, 1).Value = Sheets(i).Name
ActiveCell.Offset(1, 0).Select
Next i

Range("B3").Activate
For j = 2 To i
If (ActiveCell.Offset(0, 1).Value = "Wahr") Then
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:= _
"'" + ActiveCell.Value & "'!A1", TextToDisplay:=ActiveCell.Value
End If
ActiveCell.Offset(0, 1).Value = ""
ActiveCell.Offset(1, 0).Select
Next j
End Sub


This can be topped by assigning key F12 to jump to the tab 'Inhalt'.

 

[Susan]

this generates a userform from which you can select your tab........
i have it in my personal.xls file and attached it to a toolbar button
so i have it available for any workbook...........
===========================
Option Explicit

Sub BrowseSheets()

'Bob Phillips' code

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

Application.DisplayAlerts = True

End Sub
==========================
hope it helps!
susan