Cell Comment Summary with Hyperlinks

[ExcelMonkey]

Great Tom. Quick question. Why do you need to pass the address to two
separate variables (sStr and sStr1). One of them has extra quote
marks.




#2 11-04-2004, 01:10 PM
Tom Ogilvy
Guest Posts: n/a

Re: 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