Don't put it in the sheet module. Move it to a General module.

Yvonne_G wrote:

Dave,

I copied the UDF to the sheet module and put values and comments in A1 and
some other cells.
When I put in F1 this =echocomment(A1) it results in #NAME? Same for all
other positions.
Why? What did I do wrong?

Jack Sons
The Netherlands

"Dave Peterson" schreef in bericht
...
Saved from a previous post:

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


"Janice Lee via OfficeKB.com" wrote:

Hi,
I have multiple sheets for my spreadsheet, and some cells refer are self-
referencial of cells on another sheet.
I want to know whether it's possible to also get the comments with this =
sign. As is, it's only getting the cell value and not the comment.
Thank you!

--
Message posted via http://www.officekb.com

--

Dave Peterson

--

Dave Peterson