link comments?

[aasher20]

Hi all,

Is there a way to link comments in excel2000? I have been trying t
find a way without creating a macro, but it never gives you the optio
to paste special and paste the link to the comment, just the cell. an
if you do paste special and paste the comment, then it doesn't give yo
the option to link it. any help would be appreciated. thanks i
advance

 

[Frank Kabel]

Hi
you can't link comments with formulas

 

[Dave Peterson]

Is a UserDefinedFunction ok?

Option Explicit
Function EchoComment(FCell As Range) As Variant

Application.Volatile

Dim TCell As Range

Set TCell = Application.Caller

If TCell.Comment Is Nothing Then
'do nothing
Else
TCell.Comment.Delete
End If

If FCell.Comment Is Nothing Then
'do nothing
Else
TCell.AddComment Text:=FCell.Comment.Text
End If

If FCell.Value = "" Then
EchoComment = ""
Else
EchoComment = FCell.Value
End If

End Function

You'd use it like this:

=echocomment(a1)

The value in A1 would appear in the cell and the comment would get copied, too.

The application.volatile is there to update the comments if you change them.
(Changing the comment won't make the function run, but it'll catch up with the
next recalculation.)

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