"Go To" Prompter

sbyers2

New Member
Joined
Sep 14, 2006
Messages
5
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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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.

Code:
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

Forum statistics

Threads
1,141,153
Messages
5,704,607
Members
421,359
Latest member
Edwardvanschothorst

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top