• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

Best Toolbar/option for quick Worksheet (75+) Navigation

Hello,

I have a huge workbook in Excel 2010, with many worksheets (75+) that I have to jump around alot. The bottom left corner, right click , more sheets, then scroll is old quick.

What are some options, tool bars, etc..

In a perfect world,  some type of tool bar, that would list them all (maybe 2 or 3 columns) at once when the mouse if over it.
0
freshgrill
Asked:
freshgrill
  • 4
  • 3
1 Solution
 
Chris BottomleyCommented:
What version of excel?

Chris
0
 
freshgrillAuthor Commented:
Excel 2010
0
 
Chris BottomleyCommented:
That was a stupid question wasn't it!

One option is two pronged ... Add a sheet with all the sheets on it as shortcuts and a short cut to call up the shortcut sheet.

1. Add the code below t a module and run it.  THis creates the Table of contents.
2. Create a shortcut to the sheet:

Developer tab ... select record on the code pane:
Select the shortcut key for example shift control T (T for TOC)
OK
Select the TOC page and then stop the macro recorder

You should now be able to use shift control T to selecte the toc sheet then select your choice of sheet.

Chris
Sub Hyper()
Dim TOC As Worksheet
Dim sh As Object

    On Error Resume Next
    Set TOC = ThisWorkbook.Worksheets("TOC")
    On Error GoTo 0
    If TOC Is Nothing Then
        Set TOC = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Sheets(1))
        TOC.Name = "TOC"
    End If
    TOC.Move Before:=ThisWorkbook.Sheets(1)
    TOC.Cells.Delete
    TOC.Range("A1") = "Worksheet(s)"
    For Each sh In ThisWorkbook.Worksheets
'        TOC.Range("a" & TOC.Rows.Count).End(xlUp).Offset(1, 0) = sh.Name
        TOC.Hyperlinks.Add Anchor:=TOC.Range("a" & TOC.Rows.Count).End(xlUp).Offset(1, 0), _
            Address:="", SubAddress:=VBA.Chr(39) & sh.Name & VBA.Chr(39) & "!A1"
    Next
    TOC.Columns(1).AutoFit

End Sub

Open in new window

0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
freshgrillAuthor Commented:
Hi Chris,

Can you be a little more detailed on the steps, like how to create the module, etc... I don't see a developer tab....

Thanks,
0
 
Chris BottomleyCommented:
To display the developer tab:

File | Options | Customise Ribbon
Right hand pane - Select the check box for the developer tab

To display the VBE, alt + F11
To create a module, in the VBE insert | Module
WHen code is pasted therein close the VBE and use alt + F8 to run the code.

Chris
0
 
freshgrillAuthor Commented:
Hi Chris,

That works great, 1 question:

Is there anyway to wrap the TOC list instead of 1 long list have it split into 3 columns (example) so many more worksheets show on the screen at one time.

0
 
Chris BottomleyCommented:
Try this ... looks ok to me

Chris
Sub Hyper3()
Dim TOC As Worksheet
Dim sh As Object
Dim intCounter As Integer
Dim intOffset As Integer

    On Error Resume Next
    Set TOC = ThisWorkbook.Worksheets("TOC")
    On Error GoTo 0
    If TOC Is Nothing Then
        Set TOC = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Sheets(1))
        TOC.Name = "TOC"
    End If
    TOC.Move Before:=ThisWorkbook.Sheets(1)
    TOC.Cells.Delete
    TOC.Range("A1") = "Worksheet(s)"
    For Each sh In ThisWorkbook.Worksheets
        intCounter = intCounter + 1
        If intCounter > (ThisWorkbook.Worksheets.Count / 3) Then
            intOffset = intOffset + 1
            intCounter = 1
        End If
'        TOC.Range("a" & TOC.Rows.Count).End(xlUp).Offset(1, 0) = sh.Name
        TOC.Hyperlinks.Add Anchor:=TOC.Range("a" & intCounter + 1).Offset(, intOffset), _
            Address:="", SubAddress:=VBA.Chr(39) & sh.Name & VBA.Chr(39) & "!A1"
    Next
    TOC.Range("A1:C1").EntireColumn.AutoFit

End Sub

Open in new window

0
 
aikimarkCommented:
If you named the sheets, you could use the existing dropdown control on  the address bar.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now