link comments?

[aasher20 <>]

Hi all,

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

---
Message posted from http://www.ExcelForum.com/

[Frank Kabel]

Hi
you can't link comments with formulas

--
Regards
Frank Kabel
Frankfurt, Germany

[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

--

Dave Peterson
ec3...@msn.com