Cell Comment Summary with Hyperlinks

[ExcelMonkey]

I want to build a routine which checks all the cells in a workbook t
see if any comments exist. I then want to print the cell addresses o
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 tha
cell.

Does anyone know how to do this?

Thn

 

[Tom Ogilvy]

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.Count)).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