Quickly Tab Selection

[Mickky]

I am using Excel 2003 and i wondered if there was a way to quickly select
the tab i needed within a workbook where there are over 40 tabs.
I appreciate i can right click on the scroll arrows but this opens up a box
whereby i can view only a small seelction of the tabs...i then have to
select more sheets and then scroll to slect the one i require.
Is there a way that the entire list is viewable and therefore quicker?

Thanks in advance
Mick

 

[Héctor Miguel]

hi, Mickky !

I am using Excel 2003 and i wondered if there was a way to quickly select the tab i needed
within a workbook where there are over 40 tabs.
I appreciate i can right click on the scroll arrows but this opens up a box
whereby i can view only a small seelction of the tabs...
i then have to select more sheets and then scroll to slect the one i require.
Is there a way that the entire list is viewable and therefore quicker?

not directly (I guess), but you could assign a shortcut to a macro like...
Sub myShList()
With Application.CommandBars("workbook tabs").Controls(16)
If Right(.Caption, 3) = "..." Then .Execute Else .Parent.ShowPopup
End With
End Sub

hth,
hector.

 

[Gary Keramidas]

here's an option.

make your self a form with a command button in the lower right corner and then
paste this code in form code module

Option Explicit

Private Sub CommandButton1_Click()
Dim cntrl As control
For Each cntrl In UserForm1.Controls
If cntrl.Value = True Then
Worksheets(cntrl.Caption).Activate
unload me
End If
Next
End Sub

Private Sub UserForm_Initialize()
Dim i As Long
Dim cntrl As control
Dim ws As Worksheet
Dim x As Long
i = 1
x = 30
For i = 1 To Worksheets.Count
Set cntrl = Me.Controls.Add("Forms.optionbutton.1")
With cntrl

.Visible = True
.Width = 50
.Height = 12.5
.Left = 5
.Top = x
.Caption = Worksheets(i).Name

End With
x = x + 15
Next
Me.Height = 25 * i
End Sub

 

[kounoike]

One way
Insert worksheet, move this sheet to the most left position and name it as
you like, for example index.
open this worksheet module and paste codes below in this sheet's module.
every time you get back to this worksheet, you could see list of sheet's
name.
if you select the name of sheet you want to move, you would move to selected
sheet.


Private Sub Worksheet_Activate()
Dim i As Long, j As Long
Dim sh

Application.ScreenUpdating = False
Cells(1, 1).Select
i = 1
j = 1
Cells.ClearContents
For Each sh In Sheets
Cells(i, j) = sh.Name
i = i + 1
If i > 10 Then
Columns(j).AutoFit
i = 1
j = j + 1
End If
Next
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Sheets(Target.Value).Select
End Sub

keiji

 

[Dave]

Just an idea,
Use as index sheet, as suggested, but use hyperlinks instead of code.
Dave.

 

[Simon]

I am using Excel 2003 and i wondered if there was a way to quickly select
the tab i needed within a workbook where there are over 40 tabs.
I appreciate i can right click on the scroll arrows but this opens up a box
whereby i can view only a small seelction of the tabs...i then have to
select more sheets and then scroll to slect the one i require.
Is there a way that the entire list is viewable and therefore quicker?

Thanks in advance
Mick

Define the sheet names, e.g. Sheet1 then "Sheet1".Select

Works perfectly for me

 

[Ron de Bruin]

Hi Mickky

See
http://www.contextures.com/xlToolbar01.html

For code you can use this

Sub SheetList_RDB()
Dim ws
Dim I As Long
On Error Resume Next
ActiveSheet.Select
If Err.Number > 0 Then
MsgBox "There is no workbook open"
Err.Clear
On Error GoTo 0
Else
For Each ws In ActiveWorkbook.Sheets
If ws.Visible = True Then I = I + 1
Next ws
If I > 16 Then
Application.CommandBars("Workbook Tabs").Controls(16).Execute
Else
Application.CommandBars("Workbook Tabs").ShowPopup
End If
End If
End Sub

 

[rebelx]

In the past, I've used various "Table of Contents" add-ins that create
a new worksheet with a clickable link to all the tabs within the
workbook. I quickly did a search and found this one:

http://www.tushar-mehta.com/excel/software/maketoc/Decompiled Help/toc10.htm

Although I've never used it before, I'm sure its fairly similar to
what I've used in the past. I don't know if this is what you're asking
about, but it does give you a view of all the tabs in the workbook,
and easier navigation.