getting the comment with =

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

Use copypaste specialall on the cell with the comment. This will bring
the comment across
Pat

"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

i actually wanted to use equal sign, so that whenever i update a cell, the
cell that's self-referencial is automatically updated.
if i copy pastespecial, then i would have to do that every time i update
the value of the cell.
is there a way to get the comment with = sign?

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

Janice

You cannot copy the comment just by linking the cell to another.

A workaround of sorts.

Copy a cell with a Comment then switch to other sheet and Paste SpecialPaste
Link.

Then Paste SpecialComments.

The cell-linking will remain in effect.

If you change the original Comment you will have to re-copy it.

BTW.....I think that "self-referential" is not the proper syntax.

Cells that reference themselves cause "Circular Reference" errors.

What you are doing is referencing or linking a cell to another cell.


Gord Dibben Excel MVP


On Mon, 02 May 2005 22:21:02 GMT, "Janice Lee via OfficeKB.com"
wrote:

i actually wanted to use equal sign, so that whenever i update a cell, the
cell that's self-referencial is automatically updated.
if i copy pastespecial, then i would have to do that every time i update
the value of the cell.
is there a way to get the comment with = sign?

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

Way cool!

Gord

On Mon, 02 May 2005 19:32:19 -0500, Dave Peterson
wrote:

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,

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

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