Navigating Worksheets

  • Here's a macro that makes a hyperlinked index sheet (any chart sheets will be replaced with regular sheets with the chart as an object):


    Sub MakeIndexSheet()
    Dim i As Integer
    Dim Sheetname As String



    intResponse = MsgBox("This macro will create an index page of the worksheets in the active workbook." & vbCrLf & "Any worksheet named 'Index Sheet' will be replaced." & vbCrLf & vbCrLf & "NOTE: any chart worksheets will be converted to regular worksheets" & vbCrLf & "with the chart inserted as an object", vbInformation + vbOKCancel, "Create Index Page")
    If intResponse = vbOK Then
    On Error Resume Next
    Application.ScreenUpdating = False
    Sheets("Index Sheet").Select
    Select Case Err.Number
    Case 9
    Sheets.Add Sheets(1)
    Sheets(1).Select
    ActiveSheet.Name = "Index Sheet"
    On Error GoTo 0
    Case 0
    Application.DisplayAlerts = False
    Sheets("Index Sheet").Delete
    Application.DisplayAlerts = True
    Sheets.Add Sheets(1)
    Sheets(1).Select
    ActiveSheet.Name = "Index Sheet"
    On Error GoTo 0
    Case Else
    GoTo errorhandler
    End Select

    On Error GoTo errorhandler
    Sheetcount = ActiveWorkbook.Sheets.count
    Range("D2").Select
    ActiveCell.Value = "Contents"
    ActiveCell.Font.Size = 16
    ActiveCell.Offset(0, 1).FormulaR1C1 = _
    "To return to the index page, right-mouse click on the worksheet scroll buttons 34"
    With ActiveCell.Offset(0, 1).Characters(Start:=80, Length:=2).Font
    .Name = "Marlett"
    .FontStyle = "Regular"
    .Size = 10
    End With

    ActiveCell.Offset(2, 0).Select
    For i = 1 To Sheetcount
    If Sheets(i).Type = -4167 Then
    temp = "'" & Sheets(i).Name & "'"
    If Sheets(i).Name <> "Index Sheet" Then
    ActiveCell.Value = Left(temp, Len(temp) - 1)
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=temp & "!A1"
    '
    ' ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    ' temp & "!A1", TextToDisplay:=Left(temp, Len(temp) - 1)
    ActiveCell.Font.Size = 12
    ActiveCell.Offset(1, 0).Select
    End If
    Else 'Selected sheet is a Chart sheet
    Sheets(i).Select
    On Error Resume Next
    Sheets.Add
    On Error GoTo 0
    Sheets(i).Select
    NewSheetname = ActiveSheet.Name
    OldSheetname = Sheets(i + 1).Name
    Sheets(i + 1).Select
    ActiveChart.ChartArea.Select
    ActiveChart.Location Where:=xlLocationAsObject, Name:=NewSheetname
    ActiveSheet.Shapes(1).ScaleWidth 1.48, msoFalse, msoScaleFromBottomRight
    ActiveSheet.Shapes(1).ScaleHeight 1.48, msoFalse, msoScaleFromBottomRight
    ActiveSheet.Shapes(1).ScaleWidth 1.29, msoFalse, msoScaleFromTopLeft
    ActiveSheet.Shapes(1).ScaleHeight 1.28, msoFalse, msoScaleFromTopLeft
    DoEvents
    ActiveSheet.ChartObjects(1).Select
    With Selection.Font
    .Size = 10
    .Bold = True
    End With
    ActiveSheet.Name = OldSheetname
    Windows(ActiveWorkbook.Name).Activate
    Range("a1").Select
    ActiveWindow.DisplayGridlines = False
    With ActiveSheet.PageSetup
    .Orientation = xlLandscape
    End With
    Sheets("Index Sheet").Select
    temp = "'" & Sheets(i).Name & "'"
    ActiveCell.Value = Left(temp, Len(temp) - 1)
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=temp & "!A1"
    ActiveCell.Font.Size = 12
    ActiveCell.Offset(1, 0).Select
    End If
    Next i
    Columns("D:D").EntireColumn.AutoFit
    Range("A1").Select
    ActiveWindow.DisplayGridlines = False
    On Error Resume Next
    ActiveSheet.SetBackgroundPicture Filename:= _
    "C:\Program Files\Common Files\Microsoft Shared\Stationery\Ivy.gif"
    On Error GoTo 0
    ActiveWindow.DisplayHeadings = False
    Range("D2").Select

    End If
    errorhandler:
    If Err <> 0 Then
    MsgBox Err.Number & ": " & Err.Description
    On Error GoTo 0
    End If
    Application.ScreenUpdating = True
    End Sub

  • The quickest way is to download theasap addin from http://www.asap-utilities.com. There is a function there to create a hyperlinked index page (it's in the "Sheets" sub menu)
    This addin is free and it's invaluable.

  • Hi,


    The problem with building up an TOC-sheet is that we can easily move from the sheet to another sheet but then we need to use another "shortcut" to move to next.


    So if You don't want to build up a VBA-solution then go with the "right-click" and if You want a VBA-solution following might be of interest, whic create a Navigation-commandbar:


    Option Explicit

    Private Sub Workbook_Open()
    On Error Resume Next
    Application.CommandBars("Navigate").Delete
    On Error GoTo 0

    With Application.CommandBars.Add("Navigate XL-Dennis", , False, True)

    With .Controls.Add(msoControlButton)
    .TooltipText = "Move Back"
    .FaceId = 1017
    .OnAction = "Move_Back"
    .BeginGroup = True
    End With

    With .Controls.Add(msoControlDropdown)
    .AddItem "Sheet1"
    .AddItem "Sheet2"
    .AddItem "Sheet3"
    .TooltipText = "SheetNavigate"
    .OnAction = "Sheet_Navigate"
    End With

    With .Controls.Add(msoControlButton)
    .TooltipText = "Move next"
    .FaceId = 1018
    .OnAction = "Move_Next"
    End With

    .Protection = msoBarNoCustomize
    .Position = msoBarFloating
    .Visible = True
    End With
    End Sub

    Private Sub Sheet_Navigate()
    Dim stActiveSheet As String

    With CommandBars.ActionControl
    stActiveSheet = .List(.ListIndex)
    End With

    Select Case stActiveSheet
    Case "Sheet1"
    Worksheets("Shee1").Activate
    Case "Sheet2"
    Worksheets("Sheet2").Activate
    Case "Sheet3"
    Worksheets("Sheet3").Activate
    End Select
    End Sub

    Private Sub Move_Back()
    On Error Resume Next
    ActiveSheet.Previous.Select
    End Sub

    Private Sub Move_Next()
    On Error Resume Next
    ActiveSheet.Next.Select
    End Sub


    Kind regards,
    Dennis

  • Re: Navigating Worksheets


    Hi All,


    I was looking for a solution for navigating through worksheets, while the "right-click" option is good, I don't want every time do the right click and do the rest of the job.


    Is there any short cut way by which I can get the list of sheets displayed(preferably by using key board short cut)..


    In short I want the "right-click" option to be assigned to a key board short cut, instead of a mouse click..


    hope I made it clear


    Please help.


    thanx
    Pradeep