How to judge some cell that had been added comments?
 

Hi,
I write a macro. It add comments into some specific cells. After added
comments, I want to update comments by AddComment() method. It appear error.
So I must judge the cells whether had been added comments. Would you tell me
which methods are able to implement it? Thank you very much!

How to judge some cell that had been added comments?
 

Hi Terry,
If you try do AddComment when one already exists I think you first have
to delete the existing comment eg
ActiveSheetRange("A1").Comment.Delete.

Alternativing you can edit the comment like this:
ActiveSheetRange("A1").Comment.Shape.TextFrame.Cha racters.Text = "Blah
Blah Blah"

Also, when code is trying to do something to a comment that doesn't
exist an error results.
This error can be overcome using:

On Error Resume Next
ActiveSheetRange("A1").Comment.Shape.TextFrame.Cha racters.Text = "Blah
Blah Blah"
On Error Goto 0 'that's a zero, not an O

If A1 on Active Sheet doesn't have a comment the error message is
avoided. If A1 does have a comment it is changed to "Blah Blah Blah"

Does this help?

Ken Johnson

How to judge some cell that had been added comments?
 

Hi Terry,
sorry, my code samples all have the "." missing between ActiveSheet
and Range.

To hopefully clarify my last reply:

1) to use AddComment to edit ActiveSheet's A1 comment when A1 may or
may not already have a comment use:

On Error Resume Next
With ActiveSheet.Range("A1")
.Comment.Delete
.AddComment "Blah Blah Blah"
End With
On Error GoTo 0

2) to edit Active Sheet's A1 comment only when A1 already has a comment
use:

On Error Resume Next
ActiveSheet.Range("A1").Comment.Shape.TextFrame.Ch aracters.Text = "Blah
Blah Blah"
On Error GoTo 0

Ken Johnson

How to judge some cell that had been added comments?
 

Hi terry,

Try this,

Sub Test()
Dim rng As Range
Dim cmttext As String
cmttext = "Insert New comment text here."
Set rng = ActiveCell

If Not rng.Comment Is Nothing Then
If MsgBox("Below Comment is already present in the active
cell.Do You want to overwrite it." _
& vbNewLine & rng.Comment.Text, vbYesNo, "Insert Comment") =
vbYes Then
rng.Comment.Delete
rng.AddComment cmttext
End If
Else
rng.AddComment cmttext
End If
End Sub


Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/
http://in.geocities.com/shahshaileshs/
(Excel Add-ins Page)

*** Sent via Developersdex http://www.developersdex.com ***

How to judge some cell that had been added comments?
 

Terry, it sounds like you want to add text to a comment that is alread
there. Taking what Shah wrote and modifying it to update rather tha
overwrite, it would look like this.

Sub Test()
Dim rng As Range
Dim cmttext As String, oldtext as string

cmttext = "Insert New comment text here."
Set rng = ActiveCell

If Not rng.Comment Is Nothing Then
If MsgBox("Below Comment is already present in the active
cell.Do You want to update it." _
& vbNewLine & rng.Comment.Text, vbYesNo, "Update Comment") =
vbYes Then
oldtext = rng.comment.text
rng.Comment.Delete
rng.AddComment oldtext & chr(13) & cmttext
End If
Else
rng.AddComment cmttext
End If
End Sub

Otherwise if you just want to overwrite it, Shah's procedure i
perfect.

Jaso

--
jt
-----------------------------------------------------------------------
jtp's Profile: http://www.excelforum.com/member.php...fo&userid=2113
View this thread: http://www.excelforum.com/showthread.php?threadid=49599