?
Solved

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

Posted on 2011-03-24
8
Medium Priority
?
274 Views
Last Modified: 2012-05-11
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
Comment
Question by:freshgrill
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35208948
What version of excel?

Chris
0
 

Author Comment

by:freshgrill
ID: 35210071
Excel 2010
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35210302
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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:freshgrill
ID: 35210483
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35210557
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
 

Author Comment

by:freshgrill
ID: 35210711
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
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 total points
ID: 35211073
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
 
LVL 46

Expert Comment

by:aikimark
ID: 35212014
If you named the sheets, you could use the existing dropdown control on  the address bar.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question