Excel VBA: How To: Read/Write/Edit Cell COMMENTS [RESOLVED]

[Webtest]

Esteemed Forum Participants and Lurkers:
===============================
Excel '97, Excel 2003 VBA

Does anyone have any clues about how to Read and Write the COMMENT attached to a Cell? I have tried a few things directly from the HelpHeap, and they have all failed! Here are some things I have tried:

Dim aRange As Range
Set aRange = ActiveWorkbook.ActiveSheet.Range("E8")

'This Works!
Range("E6").Comment.Visible = True
    
'This generates an error if a comment already exists, but works the first time!
'  Application defined or object defined error.
aRange.Offset(1, 0).AddComment
    
'This works ... but ONLY IF a comment field ALREADY EXISTS
'  Object variable or With block variable not set
aRange.Offset(1, 0).Comment.Text "We CHANGED this Text!"
    
'This works whether or not a Comment already exists
aRange.Offset(2, 0).NoteText "This is TOTAL GARBAGE!"

How do I tell if a Comment field ALREADY EXISTS for a Cell?

More importantly, how do I READ an existing comment so that I can edit it?

EDIT: P.S. ... This works to Read the existing comment, but generates an error if there is no comment ...
tStr = aRange.Comment.Text

So, that still leaves the question of determining IF a comment already exists. Do I have to force a STUPID ERROR and then handle it? (I HATE that!).

Thank you for any and all comments, suggestions, and assistance.

[Webtest]

Well ... Folks ...

I've got a partial solution ... the "NoteText" offshoot from a Range Object is very helpful. It can write text into an existing comment field, or it creates a new field if a field doesn't already exist and inserts the text. It can also append text to existing text. The "999" (code below) essentially says "append the new text after the last character or after the 999th character, whichever comes first in the existing comment string".

Set aRange = Range("C2")
For i = 1 To 3
    For j = 1 To 10
        aRange.Offset(j, 5).NoteText "Pass " & i & Chr(10), 999
    Next j
Next i

I STILL would like to know how to detect the existence of a comment without having to force an error.

[DKenny]

Hi Art
Here's a possible solution. Its a fuction that iterates through the comments collection for a sheet to see if the .Parent property of that comment matches a passed range.

Function CellHasComment(ByRef ChkCell As Range) As Boolean
Dim oCom As Comment
    
    If ChkCell.Cells.Count <> 1 Then Exit Function
    
    For Each oCom In ChkCell.Worksheet.Comments
        If oCom.Parent = ChkCell Then
            CellHasComment = True
            Exit For
        End If
    Next oCom
    
End Function

[Webtest]

Thanks Declan ...

I guess that's about as elegant as it is going to get. I guess since the Comment is an object linked to a cell and not a part of the cell itself, I can't get to it directly through a cell reference.

I'm still amazed at the wealth of knowledge you have accumulated. Thanks for sharing.

[DKenny]

I guess since the Comment is an object linked to a cell and not a part of the cell itself, I can't get to it directly through a cell reference

Exactly. I really hope they change this object model in the next version of office. It would be so much nicer if the comment was a child of the cell itself.

Always glad to help.

[Webtest]

I Timed a loop on my machine (1.6 GHz P4) ... it takes about 30 millisec to scan 1000 comments. I'm concerned because there will be about 7,000 entries in my sheet, and everytime I add anything to the sheet I have to mess with the comments (add or append).

At first I thought the comment idea was a good one, but now I'm thinking I'll just cram all of the comment text into a cell instead. There are advantages and disadvantages to both methods. The Popup display was the most attractive feature of the comments.

[szophie]

I'm a novice, but I used this - though its a bit clumsy as it uses an InputBox.
To determine if there is a comment linked to the cell and edit it:

If ActiveCell.Comment Is Nothing Then
newcmnt = InputBox("", , cmnt)
ActiveCell.AddComment Text:=newcmnt
Else
cmnt = ActiveCell.Comment.Text
newcmnt = InputBox("Comment", , cmnt)
ActiveCell.Comment.Text Text:=newcmnt
End If

[PureFlaxOil]

szophie you may be a novice but in this case I think you have trumped the experts! This is just what I was looking for - many thanks.