Dear Bob,

Your suggestion has improved my problem that worksheet name listing can be
retrieved from a protected workbook, provided that I must manually open the
source workbook. However, I still need to manually make hyperlink from the
new listing to link to the protected workbook to ease the checking.

Pls check if I can change the workbook name (excel file name) at the
worksheet like defined names or enter the excel file name at a designated
cell rather than via the macro because I need to retrieve the worksheet names
from protected workbooks frequently. My problem is that I need to fill up
package files from Head Office and each workbook has many worksheets with
similar names which can be easily missed.

With Gary's macro, I can retrieve all the worksheet names and automatically
hyperlinked to the selected worksheet. However, I cannot apply the said
macro when the workbook is protected.

Thanks for your valuable assistance.

Fanny



"Bob Phillips" wrote:

Sub EasyToc()
' gsnuxx
Dim ws As Worksheet
Dim n As String
Dim p1 As String, dq As String, sq As String
dq = Chr(34)
sq = Chr(39)
p1 = "=HYPERLINK(" & dq & "#'"
p2 = "'!A1" & dq & "," & dq
k = 1
For Each ws In Workbooks("some book.xls").Worksheets
n = ws.Name
Cells(k, 1).Formula = p1 & n & p2 & n & dq & ")"
k = k + 1
Next
End Sub


--
__________________________________
HTH

Bob

"Fanny" wrote in message
...
Dear Gary,

The macro is very very useful to me. Moreover, if I want to list out the
worksheet names with hyperlink done automatically of another workbook, how
should I modify the macro?

Thanks in advance.

Fanny

"Gary''s Student" wrote:

If you run this small macro, it will produce a list of sheets that are
"clickable":

Sub EasyToc()
' gsnuxx
Dim ws As Worksheet
Dim n As String
Dim p1 As String, dq As String, sq As String
dq = Chr(34)
sq = Chr(39)
p1 = "=HYPERLINK(" & dq & "#'"
p2 = "'!A1" & dq & "," & dq
k = 1
For Each ws In Worksheets
n = ws.Name
Cells(k, 1).Formula = p1 & n & p2 & n & dq & ")"
k = k + 1
Next
End Sub

--
Gary''s Student - gsnu200816


"Niamh" wrote:

I am trying to create a list of worksheet names on my first worksheet,
similar to a table of contents. Is there a way of linking to the
worksheet
names, so this table gets updated automatically?
Thanks,
Niamh