ExcelBanter - Cell Comment Summary with Hyperlinks

Sub ListComments()
Dim cmt As Comment
Dim rng As Range
Dim sStr As String
Dim sStr1 As String
Dim sh As Worksheet
Dim sh1 As Worksheet
On Error Resume Next
Set sh1 = ActiveWorkbook.Sheets("Comments")
On Error GoTo 0
If Not sh1 Is Nothing Then
Application.DisplayAlerts = False
sh1.Delete
Application.DisplayAlerts = True
End If
With ActiveWorkbook
.Worksheets.Add(After:=.Worksheets(.Worksheets.Cou nt)).Name = "Comments"
Set rng = .Worksheets("Comments").Range("A1")
For Each sh In .Worksheets
If LCase(sh.Name) < "comments" Then
For Each cmt In sh.Comments
sStr = cmt.Parent.Parent.Name & "!" & _
cmt.Parent.Address(0, 0)
sStr1 = "'" & cmt.Parent.Parent.Name & "'!" & _
cmt.Parent.Address(0, 0)
rng.Parent.Hyperlinks.Add Anchor:=rng, Address:="", _
SubAddress:=sStr1, _
TextToDisplay:=sStr
Set rng = rng.Offset(1, 0)
Next
End If
Next
End With
End Sub

--
Regards,
Tom Ogilvy

"ExcelMonkey" wrote in message
...

I want to build a routine which checks all the cells in a workbook to
see if any comments exist. I then want to print the cell addresses of
each of these cells in a summary sheet AND as hyperlinks. For example:

Sheet1!A1
Sheet21B4
Shee3!H678

So when I click onto Sheet1!A1 in this list, it will take me to that
cell.

Does anyone know how to do this?

Thnx

--
ExcelMonkey
------------------------------------------------------------------------
ExcelMonkey's Profile:
http://www.excelforum.com/member.php...fo&userid=5221
View this thread: http://www.excelforum.com/showthread...hreadid=275200