Finding comments

[JT]

Is it possible to move through some data and determine if there is a comment
in the cell in question?

I want to go through a range of cells and if the macro finds a comment, I
want to display a msgbox.

Thanks for the help.....

 

[Jim Thomlinson]

This code should work...

Public Sub CommentsMessage()
Dim rngFoundComments As Range
Dim rng As Range

On Error Resume Next
Set rngFoundComments = Range("A1:Z100").SpecialCells(xlCellTypeComments)
On Error GoTo 0
If rngFoundComments Is Nothing Then
MsgBox "No Comments"
Else
For Each rng In rngFoundComments
MsgBox rng.Comment.Text
Next rng
End If
End Sub

 

[ryguy7272]

This is worth a look:
http://www.contextures.com/xlcomments03.html#CopyAdjacent

This too:
http://www.contextures.com/xlcomments03.html#Show

All that code comes from here:
http://www.contextures.com/xlcomments03.html#Top


HTH,
Ryan---

 

[Rick Rothstein]

Do you need to "stop" at the cell when you display the message, or would
just knowing that somewhere in the range is a cell with a comment be
sufficient? If the latter, then try something like this (change the range to
suit your needs)...

Dim R As Range
On Error Resume Next
Set R = Range("A1:Z26").SpecialCells(xlCellTypeComments)
If Not R Is Nothing Then MsgBox "The range has a comment in it."