Yeah just figured something like that out. My problem was that I was trying
to put the function in the sheet code. Once I made a module and put it in
there the code worked.

Thanks.

"Dave Peterson" wrote:


Saved from a previous post:

You can retrieve the text from a comment with a userdefined function like:

Option Explicit
Function GetComment(FCell As Range) As Variant
Application.Volatile

Set FCell = FCell(1)

If FCell.Comment Is Nothing Then
GetComment = ""
Else
GetComment = FCell.Comment.Text
End If

End Function

Then you can use it like any other function:

=getcomment(a1)

But be aware that the function won't evaluate when you just change the comment.
It'll be correct when excel recalculates. (Hit F9 to force a recalc.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Stanley wrote:

Ken,
That works great with hardcoding the cells in the range but I need to be
able to put something in a formula or something to grab the text. Is that
possible?

-Stanley

"Ken Johnson" wrote:

Hi Stanley,
to place the comment text into a cell, say B1

Public Sub read_comment()
On Error Resume Next
Range("B1").Value = Range("A1").Comment.Shape.TextFrame.Characters.Tex t
On Error GoTo 0
End Sub

Ken Johnson



--

Dave Peterson