How to know, whether a cell in Excel has a comment?

Hi Hans,

Thank you for sharing your question. I understand you're having trouble figuring out if an Excel cell has a comment and why the usual .Comment thing isn't working.

Basically, Excel changed how comments work, which can mess up code that used to work.

Now there are two kinds of comments: Notes (the old kind) and Threaded Comments (the newer, chat-style kind). Range.Commentis for the old Notes, and Range.CommentThreaded is for the new ones. If you try to use .Comment on a cell with a threaded comment (or no note at all), it can cause problems. Same goes for .CommentThreaded on a cell without a threaded comment.

Here's how to check for both safely in VBA, without selecting anything I'll give you simple code examples.

' Check a single cell for either a legacy Note or a Threaded Comment

Sub CheckCellAnnotation()

Dim rng As Range

Set rng = ThisWorkbook.Worksheets(1).Range(B64)

Dim hasNote As Boolean, hasThreaded As Boolean

' Safe check for legacy Note

On Error Resume Next

hasNote = Not (rng.Comment Is Nothing)

On Error GoTo 0

' Safe check for Threaded Comment

On Error Resume Next

hasThreaded = Not (rng.CommentThreaded Is Nothing)

On Error GoTo 0

 

If hasNote Then

MsgBox B64 has a legacy Note (Range.Comment).

ElseIf hasThreaded Then

MsgBox B64 has a Threaded Comment (Range.CommentThreaded).

Else

MsgBox B64 has no Note or Threaded Comment.

End If

End Sub

This code checks for both types of comments without causing errors.

If you need to check a bunch of cells, you can use SpecialCells for the old notes and go through CommentsThreaded for the new ones:

' Find all cells with legacy Notes in a range

Sub FindLegacyNotesInRange()

Dim rng As Range, notes As Range

Set rng = ThisWorkbook.Worksheets(1).Range(A1:D100)

 

On Error Resume Next

Set notes = rng.SpecialCells(xlCellTypeComments) ' legacy Notes only

On Error GoTo 0

 

If notes Is Nothing Then

MsgBox No legacy Notes in the range.

Else

Dim c As Range

For Each c In notes.Cells

Debug.Print c.Address, c.Comment.Text

Next c

End If

End Sub

 

' Go through all Threaded Comments on a worksheet

Sub ListThreadedComments()

Dim ct As Excel.CommentThreaded

For Each ct In ThisWorkbook.Worksheets(1).CommentsThreaded

Debug.Print ct.Parent.Address, ct.Author.Name, ct.Date, ct.Text

Next ct

End Sub

And if you just want to use the new threaded comments, Excel can convert the old ones using ActiveWorkbook.ConvertComments. This can make things simpler.

I hope this helps.

Best Regards,

Noel

so it works, maybe Microsoft has to overwork the object model

Sub CheckComment_test1()

Dim cell As Range

Set cell = Range("B65")

On Error Resume Next ' Enable error handling

Dim commentExists As Boolean

**Debug.Print cell.CommentThreaded.Text**

commentExists = **cell.CommentThreaded.Text <> Empty** ' Check if comment exists

On Error GoTo 0 ' Disable error handling

If commentExists Then

    MsgBox "Cell B64 has a comment."

Else

    MsgBox "Cell B64 does not have a comment."

End If

End Sub