Finding comments

  • Thread starter Thread starter JT
  • Start date Start date
J

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.....
 
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
 
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."
 
Back
Top