Identify and go to Excel tab

tomfarrar
tomfarrar used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
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.

Author

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.
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
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

Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Author

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?
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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.
Rob HensonFinance Analyst

Commented:
Setup of the hyperlinks on an index sheet wouldn't be that difficult. You can use the HYPERLINK function to create the links.
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

Neil FlemingConsultant and developer

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

Author

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.

Author

Commented:
Thanks all.  A lot of options...  Liked the VBA and non-VBA identified.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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.
Rob HensonFinance Analyst

Commented:
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
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

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

Author

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,,,,

Author

Commented:
Thanks again; sorry for the delay.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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
Neil FlemingConsultant and developer

Commented:
@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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial