"Go To" Prompter

[sbyers2]

I have a workbook that contains 120 tabs. I was wondering if there is a possible way/prompter to locate a specific tab, for instance, "Go to tab "5.1.1". Im just looking for a shorter alternative to scrolling all the way across the bottom.

 

[njimack]

This may be of use. I can't remember where I found this code (I've tweaked it slightly), but it creates a pop-up which lists all visible sheets, and allows you to select them from a drop-down list.

Option Explicit

Sub Auto_Close()

    On Error Resume Next
    Application.CommandBars("Sheet_Navigator").Delete
    On Error GoTo 0
End Sub

Sub Auto_Open()

    Dim cb As CommandBar
    Dim ctrl As CommandBarControl

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

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

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

Call Refresh
End Sub
Sub Change_Sheet()

    Dim myWksName As String
    Dim wks

    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 = Sheets(myWksName)
    On Error GoTo 0

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

End Sub
Sub Refresh()
    Dim ctrl As CommandBarControl
    Dim wks
    
    On Error GoTo Finish:
    Set ctrl = Application.CommandBars("Sheet_Navigator") _
                      .FindControl(Tag:="__wksnames__")
    ctrl.Clear

    On Error Resume Next
    For Each wks In ActiveWorkbook.Sheets
        If wks.Visible = xlSheetVisible Then
            ctrl.AddItem wks.Name
        End If
    Next wks
Finish:
End Sub

 

[pgc01]

Hi sbyers2

If I understand correctly you already have that in excel.

Right-click on the arrows that you use to scroll through the sheets. You'll have a list of 15 worksheets, and if you choose "More Sheets..." you have a list box with all the sheets.

Hope this is what you want
PGC