Menu of Worksheet Tabs

[Thomas M.]

Excel 2007

We have a workbook that contains a lot of tabs and it is sometimes hard to
find the right tab. This morning, I was hit with the idea of inserting
another sheet that would contain a hyperlinked menu of all the tabs. That
would allow me to use CTRL+F to search on a keyword and quickly find the
entry for the tab that I need. Since the list would be hyperlinked, I could
then click the link and be taken to the tab.

I'm not married to the idea of having a separate sheet with a hyperlinked
list. If there is a better way to do it I would be open to those
suggestions. Just as long as the basic functionality described above is
there.

Any ideas?

Thanks for any help that you can offer!

--Tom

 

[Don Guillett]

use this to make a list
sub makelistofshts()
for i=1 to sheets.count
cells(i,1)=sheets(i).name
next i
end sub


I use this in the sheet module of my menu sheet by double clicking on the
typed name of the sheet.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(WantedSheet) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Application.GoTo Sheets(WantedSheet).Range("a4")
End If
Application.DisplayAlerts = True
End Sub

 

[Jim Cone]

There is some more VBA code (for XL2007+ only) that will add a hyperlinked Table of Contents (TOC) at ...
http://blogs.msdn.com/excel/archive/2010/04/30/creating-a-toc-with-hyperlinks-programmatically.aspx

None of the "free" code I have seen handles errors, hidden sheets or chart sheets.
--
Jim Cone
Portland, Oregon USA
('Extras for Excel' add-in to create a TOC and do other nice stuff (3 week free trial)
Email and ask for it... (e-mail address removed) ) remove xxx



"Thomas M." <[email protected]>
wrote in message Excel 2007
We have a workbook that contains a lot of tabs and it is sometimes hard to
find the right tab. This morning, I was hit with the idea of inserting
another sheet that would contain a hyperlinked menu of all the tabs. That
would allow me to use CTRL+F to search on a keyword and quickly find the
entry for the tab that I need. Since the list would be hyperlinked, I could
then click the link and be taken to the tab.

I'm not married to the idea of having a separate sheet with a hyperlinked
list. If there is a better way to do it I would be open to those
suggestions. Just as long as the basic functionality described above is
there.

Any ideas?

Thanks for any help that you can offer!

--Tom

 

[Dave Peterson]

Maybe you could use David McRitchie's Build Table of Contents to get the list
and the hyperlinks all at once:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

Another option if you want a more generic solution:
http://contextures.com/xlToolbar01.html
(From Debra Dalgleish's site.)

It builds a toolbar that you can use with any workbook to navigate to any
worksheet. There's a link on that site for an xl2007 version for the ribbon
(from Ron de Bruin).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Shane Devenshire]

Hi,

I use a code solution with a list of all the sheet names on the first tab
and then double-click the sheet name and move to it. If the other
suggestions don't suffice, reply back and I will send you the example after
work this everning.

 

[Mike Middleton]

Tom -

Note the four navigation arrows to the left of the worksheet tabs.

If you right-click one of the arrows in that area, you get a shortcut menu
of the worksheet tabs.

- Mike
http://www.MikeMiddleton.com

 

[Thomas M.]

That should work! Now if I can find a way to quickly alphabetize the
sheets, I would be all set, but the way the tabs were named (not be me) I
think that I'll be stuck with renaming the sheets and then dragging and
dropping into order.

--Tom

 

[ozgrid.com]

Hi Tom

See http://www.ozgrid.com/VBA/sort-sheets.htm