Navigatng worksheets in a large Workbook

[Trying To Excel]

Is there a shortcut to go back and forth to worksheets that are used often
but not readily available. For exam;ple sheet 25,28,31etc.

[PirateSam]

Hello, you could try right clicking on the arrows at the lower left hand side
of the sheet. This should show all sheets in your workbook,
Regards, Nick.


"Trying To Excel" wrote:

> Is there a shortcut to go back and forth to worksheets that are used often
> but not readily available. For exam;ple sheet 25,28,31etc.

[kghexce]

With a large 500 sheet workbook an index page has proved invaluable.

On one sheet (called 'GoToSheet') links have been created to all 500 pages. EG

=HYPERLINK("#Sheet1!A1",1)
=HYPERLINK("#Sheet2!A1",2)
=HYPERLINK("#Sheet3!A1",3)
=HYPERLINK("#Sheet4!A1",4)
=HYPERLINK("#Sheet5!A1",5)
=HYPERLINK("#Sheet6!A1",6)
and so on up to
=HYPERLINK("#Sheet500!A1",500)

A return link to the index page is put on each of the 500 sheets to both the index page and a summary
=HYPERLINK("#Summary!A1","Summary")
=HYPERLINK("#GoToSheet!A1","GoToSheet")

To create the links, entered the various items in separate columns, incremented the sheet and description columns and then concatenated across all the columns to create the 500 links, copied the links and pasted into the 'GoToSheet' Sheet.

Manually created the return links (for those who didn't like using F5) on Sheet1, copied and then pasted across all remaining 499 sheets at once.

There are VBA solutions which I tried, but I prefer the index page.

[Dave Peterson]

How about a toolbar that you can use with any workbook?

If you want to try one, start a new workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side. Paste this code
there.

Option Explicit
Sub auto_close()
On Error Resume Next
Application.CommandBars("MyNavigator").Delete
On Error GoTo 0
End Sub

Sub auto_open()

Dim cb As CommandBar
Dim ctrl As CommandBarControl
Dim wks As Worksheet

On Error Resume Next
Application.CommandBars("MyNavigator").Delete
On Error GoTo 0

Set cb = Application.CommandBars.Add(Name:="myNavigator", temporary:=True)
With cb
.Visible = True
Set ctrl = .Controls.Add(Type:=msoControlButton, temporary:=True)
With ctrl
.Style = msoButtonCaption
.Caption = "Refresh Worksheet List"
.OnAction = ThisWorkbook.Name & "!refreshthesheets"
End With

Set ctrl = .Controls.Add(Type:=msoControlComboBox, temporary:=True)
With ctrl
.Width = 300
.AddItem "Click Refresh First"
.OnAction = ThisWorkbook.Name & "!changethesheet"
.Tag = "__wksnames__"
End With
End With

End Sub
Sub ChangeTheSheet()

Dim myWksName As String
Dim wks As Worksheet

With Application.CommandBars.ActionControl
If .ListIndex = 0 Then
MsgBox "Please select an existing sheet"
Exit Sub
Else
myWksName = .List(.ListIndex)
End If
End With

Set wks = Nothing
On Error Resume Next
Set wks = Worksheets(myWksName)
On Error GoTo 0

If wks Is Nothing Then
Call RefreshTheSheets
MsgBox "Please try again"
Else
wks.Select
End If

End Sub
Sub RefreshTheSheets()
Dim ctrl As CommandBarControl
Dim wks As Worksheet

Set ctrl = Application.CommandBars("myNavigator") _
.FindControl(Tag:="__wksnames__")
ctrl.Clear

For Each wks In ActiveWorkbook.Worksheets
If wks.Visible = xlSheetVisible Then
ctrl.AddItem wks.Name
End If
Next wks
End Sub

Now back to excel and
file|saveas
choose save as type: Microsoft Office Excel Add-in (*.xla)
at the bottom of that dropdown.

Now close excel and reopen it.
Turn on the addin.
tools|addins
look for that workbookname you just created and put a check mark there.

You should see a toolbar that you can position where you want.

If you swap workbooks, just click the other button to get a fresh list of
worksheet names in the dropdown.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Trying To Excel wrote:
>
> Is there a shortcut to go back and forth to worksheets that are used often
> but not readily available. For exam;ple sheet 25,28,31etc.

--

Dave Peterson

[Gord Dibben]

One more from Bob Phillips

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 Excel MVP

On Tue, 20 Dec 2005 22:59:02 -0800, Trying To Excel
<[email protected]> wrote:

>Is there a shortcut to go back and forth to worksheets that are used often
>but not readily available. For exam;ple sheet 25,28,31etc.