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.

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.Tex t
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

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

Yes that is what I am looking for. Thank you.

"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

Hi Stanley,
You're welcome, glad I could help.
Ken Johnson

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

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

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