Removal of author from Comments

[Jim May]

I currently have a line of code:

MsgBox "You have comment which says " & rCell.Comment.Text

which displays the FULL Comments area.
Can I EXCLUDE the AUTHOR text? If so,
How?

TIA,

[Tom Ogilvy]

Only by parsing it out.

the whole think is just a single text string
Sub ae()
Dim rCell As Range
Dim sStr As String
Dim ipos As Long
Set rCell = Range("D6")
sStr = rCell.Comment.Text
ipos = InStr(1, sStr, ":", vbTextCompare)
sStr = Trim(Right(sStr, Len(sStr) - ipos))
MsgBox "You have comment which says " & sStr

End Sub

--
Regards,
Tom Ogilvy

"Jim May" <jm...@cox.net> wrote in message
news:X8M5f.2179$AO5.1064@dukeread01...

[Random Poster]

"Tom Ogilvy" <twog...@msn.com> wrote in news:#U0bYyX1FHA.1040
@TK2MSFTNGP14.phx.gbl:

> Only by parsing it out.
>
> the whole think is just a single text string
> Sub ae()
> Dim rCell As Range
> Dim sStr As String
> Dim ipos As Long
> Set rCell = Range("D6")
> sStr = rCell.Comment.Text
> ipos = InStr(1, sStr, ":", vbTextCompare)
> sStr = Trim(Right(sStr, Len(sStr) - ipos))
> MsgBox "You have comment which says " & sStr
>
> End Sub
>

Tom's too quick for me!! I put together a function that does the same
type of thing. The main difference is that it looks for the colon
followed by a new line.

I don't know about others but I delete the author line of almost every
comment I create. A list of potential authors (Application.Username)
might be useful.

Another option might be to look at the font and remove the bold
characters from the beginning.

HTH

Public Function CommentText(c As Range, _
Optional RemoveAuthor As Boolean = False)

Dim CommentLength As Integer
Dim CommentStart As Integer

If c.Comment Is Nothing Then
CommentText = ""
Exit Function
End If

CommentText = c.Comment.Text
CommentLength = Len(CommentText)
CommentStart = InStr(1, CommentText, ":" & Chr(10), vbTextCompare)
CommentStart = CommentStart + IIf(CommentStart > 0, 1, 0)

If RemoveAuthor Then _
CommentText = Mid(CommentText, CommentStart, CommentLength)

End Function