Hello,

I have a workbook with 120 worksheets. I created an Index worksheet with
all of the names of each worksheet (A2-A121). Now I wish to somehow easily
hyperlink the index names to the worksheets. The worksheets and the index
are named exactly the same. Thanks for looking,

Mj
--
Thank You!

Take a look here:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm

In article <DAC4A5D6-0EE5-40E7-A2EA-(E-Mail Removed)>,
maijiuli <(E-Mail Removed)> wrote:

> Hello,
>
> I have a workbook with 120 worksheets. I created an Index worksheet with
> all of the names of each worksheet (A2-A121). Now I wish to somehow easily
> hyperlink the index names to the worksheets. The worksheets and the index
> are named exactly the same. Thanks for looking,
>
> Mj

Here's a little VB routine I use to create a TOC sheet. HTH
Sub createTOC()
Dim ws As Worksheet, wsNw As Worksheet
Dim n As Integer
Set wsNw =
ActiveWorkbook.Worksheets.Add(Before:=ActiveWorkbook.Sheets(1))
With wsNw
starter:
On Error GoTo errHandler
.Name = "TOC"
On Error GoTo 0
.[a1] = "Table Of Contents"
.[a2] = ActiveWorkbook.Name & " Worksheets"
.[a1].Font.Size = 14
.[a2].Font.Size = 10
n = 4
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> .Name And ws.Visible = True Then
.Cells(n, 1) = ws.Name
.Hyperlinks.Add _
Anchor:=.Cells(n, 1), _
Address:="", _
SubAddress:="'" & ws.Name & "'!A1"
n = n + 1
End If
Next
End With
Columns("A:A").EntireColumn.AutoFit
Exit Sub
errHandler: Application.DisplayAlerts = False
Sheets("TOC").Delete
Application.DisplayAlerts = True
GoTo starter
Error1:
MsgBox "No workbook open", vbCritical, "Error"
End Sub

maijiuli wrote:
> Hello,
>
> I have a workbook with 120 worksheets. I created an Index worksheet with
> all of the names of each worksheet (A2-A121). Now I wish to somehow easily
> hyperlink the index names to the worksheets. The worksheets and the index
> are named exactly the same. Thanks for looking,
>
> Mj
> --
> Thank You!

Create a dropdrown object and attach a list to it using the range then on the
change event type:

Private Sub ComboBox1_Change()

WkName=Range(LinkedCell) ' The linked cell is the same you have selected on
the ComboBox Properties.
Worksheets(wkName).select

End Sub




--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"maijiuli" wrote:

> Hello,
>
> I have a workbook with 120 worksheets. I created an Index worksheet with
> all of the names of each worksheet (A2-A121). Now I wish to somehow easily
> hyperlink the index names to the worksheets. The worksheets and the index
> are named exactly the same. Thanks for looking,
>
> Mj
> --
> Thank You!

Thank you. A lot of stuff to go through but the answer is within.
--
Thank You!


"JE McGimpsey" wrote:

> Take a look here:
>
> http://www.mvps.org/dmcritchie/excel/buildtoc.htm
>
> In article <DAC4A5D6-0EE5-40E7-A2EA-(E-Mail Removed)>,
> maijiuli <(E-Mail Removed)> wrote:
>
> > Hello,
> >
> > I have a workbook with 120 worksheets. I created an Index worksheet with
> > all of the names of each worksheet (A2-A121). Now I wish to somehow easily
> > hyperlink the index names to the worksheets. The worksheets and the index
> > are named exactly the same. Thanks for looking,
> >
> > Mj
>

Thanks JW, exactly what I was looking for.
--
Thank You!


"JW" wrote:

> Here's a little VB routine I use to create a TOC sheet. HTH
> Sub createTOC()
> Dim ws As Worksheet, wsNw As Worksheet
> Dim n As Integer
> Set wsNw =
> ActiveWorkbook.Worksheets.Add(Before:=ActiveWorkbook.Sheets(1))
> With wsNw
> starter:
> On Error GoTo errHandler
> .Name = "TOC"
> On Error GoTo 0
> .[a1] = "Table Of Contents"
> .[a2] = ActiveWorkbook.Name & " Worksheets"
> .[a1].Font.Size = 14
> .[a2].Font.Size = 10
> n = 4
> For Each ws In ActiveWorkbook.Worksheets
> If ws.Name <> .Name And ws.Visible = True Then
> .Cells(n, 1) = ws.Name
> .Hyperlinks.Add _
> Anchor:=.Cells(n, 1), _
> Address:="", _
> SubAddress:="'" & ws.Name & "'!A1"
> n = n + 1
> End If
> Next
> End With
> Columns("A:A").EntireColumn.AutoFit
> Exit Sub
> errHandler: Application.DisplayAlerts = False
> Sheets("TOC").Delete
> Application.DisplayAlerts = True
> GoTo starter
> Error1:
> MsgBox "No workbook open", vbCritical, "Error"
> End Sub
>
> maijiuli wrote:
> > Hello,
> >
> > I have a workbook with 120 worksheets. I created an Index worksheet with
> > all of the names of each worksheet (A2-A121). Now I wish to somehow easily
> > hyperlink the index names to the worksheets. The worksheets and the index
> > are named exactly the same. Thanks for looking,
> >
> > Mj
> > --
> > Thank You!
>
>

Thanks for looking Michael. JW's answer did the trick already.
--
Thank You!


"Michael" wrote:

> Create a dropdrown object and attach a list to it using the range then on the
> change event type:
>
> Private Sub ComboBox1_Change()
>
> WkName=Range(LinkedCell) ' The linked cell is the same you have selected on
> the ComboBox Properties.
> Worksheets(wkName).select
>
> End Sub
>
>
>
>
> --
> If this posting was helpful, please click on the Yes button.
> Regards,
>
> Michael Arch.
>
>
>
>
> "maijiuli" wrote:
>
> > Hello,
> >
> > I have a workbook with 120 worksheets. I created an Index worksheet with
> > all of the names of each worksheet (A2-A121). Now I wish to somehow easily
> > hyperlink the index names to the worksheets. The worksheets and the index
> > are named exactly the same. Thanks for looking,
> >
> > Mj
> > --
> > Thank You!