How do I reference the comments of a cell?

[Guest]

Can I reference the comments of a cell in a formula?

For example, in cell B1, I want to type something like =A1.Comment

 

[Ardus Petus]

dim sText as string
sText=Range("A1").Comment.Text

HTH

 

[Ken Johnson]

Hi BrotherSun,

Paste this User Defined Function into a standard module in the VBA
Editor of your workbook...

Public Function CELCOM(rgCELL As Range) As String
On Error GoTo NO_COMMENT
CELCOM = "Comment in " & _
rgCELL.Address(False, False) & _
":= " & _
rgCELL.Comment.Text
NO_COMMENT:
End Function

Example, say A1 has the comment "Have a nice day" and in Z1 you
want to view A1's comment, then, in Z1 type..

=CELCOM(A1)

After pressing Enter Z1 will contain...

Comment in A1:= Have a nice day

You can insert the function either by typing it in or by going
Insert>Function then on the Paste Function dialog select the All
category then the CELCOM function.

The function is not volatile, ie if the function is already in place in
a cell and the cell comment it refers to is changed, it will not
automatically update. So, to ensure that the CELCOM function returns
current comment, Press F9 to force the workbook to calculate.

Also, if the comment it refers to is multi-lined and you want the
CELCOM function result to multi-lined then you will have to format the
cell with the CELCOM function to have Text Wrapping.

If you are unsure as to how to get the function code in place, then...

1. Copy the code
2. Press Alt + F11 to get to the VBA Editor
3. Go Insert>Module then paste the code into that module.
4. Press Alt + F11 to return to the worksheet.

Ken Johnson