Retrieving Comments

S

Stanley

Is it possible either through macro or formula (or any method for that
matter) to grab the comments and have them added to a specific cell? I know
that I can have them print at the bottom of the page and all that but I need
to use the comments on another tab.
 
K

Ken Johnson

Hi Stanley,
the following sub displays a messagebox with the text in the comment in
A1

Public Sub read_comment()
On Error Resume Next
MsgBox Range("A1").Comment.Shape.TextFrame.Characters.Text
On Error Goto 0
End Sub

If there is no comment in A1 an error occurs. "On Error Resume Next"
and "On Error Goto 0" are included to handle that error.

Does this help?

Ken Johnson
 
K

Ken Johnson

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.Text
On Error GoTo 0
End Sub

Ken Johnson
 
S

Stanley

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
 
D

Dave Peterson

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
 
S

Stanley

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.
 
Top