If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Adding default comment text | dshigley | General Discussion | 1 | April 8th, 2005 05:26 PM |
"Edit Comment" Broken in Excel 2002 SP2 | Gary Johnson | General Discussion | 5 | October 14th, 2004 06:13 PM |
Comment edit, fails to exit | Walter Whipple | General Discussion | 1 | June 28th, 2004 03:35 PM |
Unreadable Comment Boxes | Gordon | General Discussion | 0 | June 11th, 2004 10:52 PM |
Microsoft Excel - "Edit Cell Comment" abnormality | Maia | Worksheet Functions | 0 | March 9th, 2004 12:46 AM |