freshgrill
asked on
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.
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.
ASKER
Excel 2010
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
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 SubASKER
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,
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,
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you named the sheets, you could use the existing dropdown control on the address bar.
Chris