Creating Link

[John Telly]

I have a spreadsheet with multiple worksheets and would
like to create a summary sheet where I list the names of
all the worksheets and allow the user to click on the name
of the desired worksheet and automatically go to that
sheet.
How may I achieve this?

 

[Dave Peterson]

Try David McRitchie's build table of contents macro:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

David also has notes on how to install macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[igor]

The easiest way is to create hyperlink (insert-hyperlink)
chooshe "Named location in file" and follow the steps

good luck

 

[Marty]

This code will create a combo box of all you sheets. When
the user selects an item in the combo box that's the sheet
that will be displayed. It's not perfect because I do not
know what you want to achieve but it's 98% there. All you
need to do is open a VBA editor in Excel and paste this
code. If you do not know how to do this, please respond
to me directly and I'll tell you how. By the way you will
need to click Tools > References in the VBA window and
make sure all of the Microsoft Active X 2.7 controls are
loaded. Again, if you do not know how let me know.

Public VariSub
Dim MoveToSheet As String
Dim intI As Integer, TotalIntI As Integer, TitleSheet


Private Sub ComboBox1_Change()

MoveToSheet = ComboBox1.Value
Worksheets(MoveToSheet).Activate

End Sub
Private Sub CommandButton1_Click()
Worksheets(TitleSheet).Delete
Unload UserForm1

End Sub

Private Sub UserForm_Initialize()
Dim WSname As String, WSarr(254) As String
Application.DisplayAlerts = False
intI = -1
For Each WS In Worksheets
intI = intI + 1
WSname = WS.NAME
WSarr(intI) = WSname
Next
TotalIntI = intI + 1
intI = 0
Worksheets().Add
TitleSheet = ActiveSheet.NAME
Worksheets(WSarr(0)).Activate

For TotalIntI = 1 To TotalIntI
Range("a" & intI + 1).Value = WSarr(intI)
ActiveCell.Offset(1).Activate
intI = intI + 1
Next
ComboBox1.RowSource = "a1:a" & TotalIntI - 1

End Sub

 

[Marrty]

I forgot to mention, you will need to create a FORM with a
ComboBox (ComboBox1) and a Command button (CommandButton1)
in order for this to work.

Marty