Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding default comment text | Excel Discussion (Misc queries) | |||
Hotkey problem w/ macro to append comment | Excel Discussion (Misc queries) | |||
New Excel Comment Box Issue | Excel Discussion (Misc queries) | |||
comment indicators should feature lock or pw protect limiting acc. | Excel Worksheet Functions | |||
a comment plugin & copy paste directly from excel to comment ? fr. | Excel Worksheet Functions |