Identify and go to Excel tab

I have a spreadsheet with probably more than 100 tabs each identified by a 6 digit number. I want to be able to easily go to a specific tab rather than scrolling through the tabs or the working from the random list. Is this doable?
LVL 7
tomfarrarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
At the bottom left of the Excel screen there is the tab navigation arrows. If you right click on these you will get a list of all sheets. With 100 tabs there will still be some scrolling but might be easier than scrolling through the tabs.

Alternatively, you could set up an index sheet with a list of a sheet names and a hyperlink to take you to each sheet.
0
tomfarrarAuthor Commented:
Thanks, Rob, I knew about the first option but was hoping for more.  The hyperlink could work but it would require a lot of initial setup and ongoing maintenance which I was hoping to avoid.  But I will think on that one.
0
byundtCommented:
You didn't mention how you know which worksheet to go to. Knowing that would be helpful in deciding the best way to go to that other worksheet.

If the other worksheet name is listed in a cell, you could trap the event when a user doubleclicks that cell and use Workbook_SheetBeforeDoubleClick event sub to activate that other sheet. The code to do it must go in the ThisWorkbook code pane and won't work at all if you install it anywhere else.
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Worksheets(Target.Value)
On Error GoTo 0
If Not ws Is Nothing Then
    Cancel = True
    ws.Activate
End If
End Sub

Open in new window

Another approach would be to run a macro that asks the user for the sheet name. If that name is valid, the worksheet will be activated. Otherwise, an error message will be displayed.

This macro should go in a regular module sheet. You can put a button calling it on the worksheet(s) where it might be useful. Or you might add some ribbon XML to put a button calling the macro on the ribbon. Or you might use the ALT + F8 macro selector to call the macro.
Sub FindMySheet()
Dim s As String
Dim ws As Worksheet
s = InputBox("Which worksheet do you want to go to?")
On Error Resume Next
Set ws = ThisWorkbook.Worksheets(s)
On Error GoTo 0
If Not ws Is Nothing Then
    ws.Activate
Else
    MsgBox "There is no worksheet by that name. Please check the name and rerun the macro."
End If
End Sub

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

tomfarrarAuthor Commented:
byundt -- Good points. I first was hoping there might be a built-in Excel function to help do this, but I guess I was just hoping.  The idea way to select would be to enter the six-digit number (ex. 234567) and it would go to that tab.  I think that is what you've lined out in the first macro, right?
0
byundtCommented:
Actually, the second macro does exactly what you are now saying. Run the macro, it asks which sheet you want, enter the six-digit number, and the macro takes you there.

The first macro tries to be even simpler. If you already had the six-digit number in a cell, just doubleclick that cell and an event sub takes you to that worksheet.
0
Rob HensonFinance AnalystCommented:
Setup of the hyperlinks on an index sheet wouldn't be that difficult. You can use the HYPERLINK function to create the links.
0
Ejgil HedegaardCommented:
With Robs idea, you could have a macro to update the hyperlinks sheet.
Make a sheet with the name Index and run this macro.
Option Explicit

Sub MakeSheetIndex()
    Dim ws As Worksheet, wsIndex As Worksheet
    Dim rw As Integer
    
    Set wsIndex = ThisWorkbook.Worksheets("Index")
    wsIndex.Cells.Clear
    wsIndex.Range("A1") = "Sheet name"
    rw = 1
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> wsIndex.Name Then
            rw = rw + 1
            wsIndex.Cells(rw, 1).Formula = "=HYPERLINK(" & "'" & ws.Name & "'!A1,""" & ws.Name & """)"
        End If
    Next ws
    wsIndex.Range("A1").CurrentRegion.Sort Key1:="Sheet name", Order1:=xlAscending, Header:=xlYes
    wsIndex.Columns.AutoFit
End Sub

Open in new window

0
Neil FlemingConsultant and developerCommented:
Another approach would be to use a UserForm equipped with a listbox, as in the attached sample.

The Userform reads all the worksheet names when activated. Double-clicking in the list takes you to the selected sheet.

A macro to open the Userform, showPicker, is in the code module mSheets. You could attach this to a button in the Quick Access toolbar, for instance.

Here is the code in the userform, which is very simple.

Option Explicit


Private Sub lbSheets_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'activate chosen sheet
Worksheets(lbSheets.Value).Activate
Me.Hide
End Sub

Private Sub UserForm_Activate()
Dim ws As Worksheet

'clear the list box
lbSheets.Clear
'reload the listbox
For Each ws In Worksheets
lbSheets.AddItem ws.Name
Next

End Sub

Open in new window


The upside of this approach is you do not need to go back to an "index sheet" each time you want to move sheets.
multisheet.xlsm
0
abbas abdullaCommented:
Hi Tom,

I will offer you non vba solution using Hyperlink formula =HYPERLINK(CONCATENATE("#","Home!A3"),"Go Home"). See the attached file.

Br,
Abbas Abdulla
Hyperlink-GoTo-Formula.xlsx
0
tomfarrarAuthor Commented:
Wow..  Lot of options here.  I am looking at closely now.  Sorry for the slow response.  It has been a busy time for me working with the data.
0
tomfarrarAuthor Commented:
Thanks all.  A lot of options...  Liked the VBA and non-VBA identified.
0
byundtCommented:
A non-VBA trick that I was not aware of until yesterday may be relevant. It's hard to beat no formulas and no code for a solution to a problem.

If you clear the checkbox for "Allow editing directly in cells" in the File...Options...Advanced menu item, you can doubleclick a cell with a formula returning a value from another worksheet and Excel will take you there tout de suite.
1
Rob HensonFinance AnalystCommented:
Would that work with INDIRECT? I suspect not.

So you'd have to manually/programmatically setup:
=Sheet1!A1
=Sheet2!A1
=Sheet3!A1
etc etc through to
Sheet100!A1
0
byundtCommented:
Rob,
My original question was how do you know which worksheet you want to view? tomfarrar hasn't said so far, but the answer will make it clear which is the best way to implement the required linkages.

Since the worksheets are named with an arbitrary six-digit number, you sure won't be pulling that info out the top of your head. That's why I thought there might already be data on the worksheet the user is looking at that provide the name of the target worksheet. If the data are formulas linking to certain cells on each worksheet, then the best solution to the problem will leverage that fact.

Brad
0
tomfarrarAuthor Commented:
Hi All - Thought I had closed this out with my previous comment, but obviously I didn't.  

"Thanks all.  A lot of options...  Liked the VBA and non-VBA identified."
be
I will award now.  As to  byundt's comment:  The selection process could have worked  either way after thinking it through.  I could have built a list of the tab numbers on a separate sheet, but that would be a maintenance issue as more tabs are added (and there are many).  The better solution (as I perhaps poorly stated) was to enter the number and have the "procedure" take me to that page.  Anyway,,,,
0
tomfarrarAuthor Commented:
Thanks again; sorry for the delay.
0
byundtCommented:
tomfarrar,
While I appreciate that you have found good solutions to your problem, I still feel that you haven't answered my question about how does the user know which worksheet he wants to go to? I am trying to focus on the user's needs. Entering the number and having a "procedure" take you to that page is OK, but doesn't seem like the friendliest way of addressing the user's needs.

Somehow, the user must decide which worksheet they want to go to. My question is how do they reach that decision? Does the user see that other worksheet name displayed somewhere? Does the user see data taken from that other worksheet (such as by a formula) and want to go there to investigate? Has the user memorized all the six digit codes and just knows which one they need next? Does the user play "spin the bottle" and choose a destination randomly?

Brad
0
Neil FlemingConsultant and developerCommented:
@Brad - that's the upside of the UserForm approach, I think. You can add more data to the menu, and go anywhere from any sheet, without having to jump back to an index sheet.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.