Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Posts
    158
    Thanks
    30
    Thanked 0 Times in 0 Posts

    Including comments in referenced cell

    Can I get a referenced cell to include its comment?
    If A1 worksheet 1 has a comment and I want to reference it in A1 worksheet 2 including its comment.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,089
    Thanks
    39
    Thanked 190 Times in 177 Posts
    Skipro,

    Here is a UDF that you can call from any cell and reference another. The comment as well as the value will be copied. In this example, Sheet2 Cell A1 pulls the value and copies the comment from Sheet1 Cell A1

    Sheet2 Cell A1 =CopyComment(Sheet1!A1)

    Code:
    Public Function Comment2Comment(Rng As Range) As Variant
        msg = Rng.Comment.Text
        ActiveCell.ClearComments
        ActiveCell.AddComment
        ActiveCell.Comment.Text Text:=msg
        Comment2Comment = Rng.Value
    End Function
    There is one cravat: Updating the cell value on Sheet 1 Cell A1 will update the value in the cell with the call but the comment if edited will not update. Refreshing the macro in the calling cell will update the comment.

    HTH,
    Maud
    Last edited by Maudibe; 2014-01-13 at 21:30.

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,198
    Thanks
    14
    Thanked 329 Times in 322 Posts
    Another caveat:
    This is being called as a worksheet function, not a VBA function. The active cell may not allows be the cell with the formula, so the comment may be created in a different cell than anticipated and it tends not to update.

    This may be a better approach. It will update when a calculation is done and it will update the comment on the cell that calls the function, not the active cell. This will work better if you use the function in more than 1 cell in the workbook.

    Code:
    Public Function Comment2Comment(Rng As Range) As Variant
      Dim sMsg As String
      With Application
        .Volatile
      sMsg = Rng.Comment.Text
        With .Caller
          .ClearComments
          .AddComment
          .Comment.Text Text:=sMsg
        End With
      End With
      Comment2Comment = Rng.Value
    End Function
    Steve

  5. #4
    2 Star Lounger
    Join Date
    Dec 2009
    Posts
    158
    Thanks
    30
    Thanked 0 Times in 0 Posts
    Maud & Steve,
    Thank you.

    Maud,
    Did you mean, "Sheet2 Cell A1 =Comment2Comment(Sheet1!A1)", not "Sheet2 Cell A1 =CopyComment(Sheet1!A1)"

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,198
    Thanks
    14
    Thanked 329 Times in 322 Posts
    Maud means Comment2Comment, that is the name of the function.

    Steve

  7. #6
    2 Star Lounger
    Join Date
    Dec 2009
    Posts
    158
    Thanks
    30
    Thanked 0 Times in 0 Posts
    Steve,
    When I use your function but do not have a comment in 1st cell, I get #Value!. Add a comment, OK. Delete comment get #Value! but does not remove the comment in cell 2.

  8. #7
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,089
    Thanks
    39
    Thanked 190 Times in 177 Posts
    Yes Skipro, that is what I meant. The formula in Sheet2 Cell A1 is =Comment2Comment(Sheet1!A1)

  9. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,198
    Thanks
    14
    Thanked 329 Times in 322 Posts
    Yes the presumption of the UDF is that the range called will have a comment, and there will be an error if not. This correction checks for an error. Also if there is no comment the comment is cleared in the caller cell.

    Code:
    Option Explicit
    Public Function Comment2Comment(Rng As Range) As Variant
      Dim sMsg As String
      With Application
        .Volatile
      On Error Resume Next
      sMsg = Rng.Comment.Text
      On Error GoTo 0
        With .Caller
          .ClearComments
          If sMsg <> "" Then
            .AddComment
            .Comment.Text Text:=sMsg
          End If
        End With
      End With
      Comment2Comment = Rng.Value
    End Function
    Steve

  10. The Following User Says Thank You to sdckapr For This Useful Post:

    skipro (2014-01-16)

  11. #9
    2 Star Lounger
    Join Date
    Dec 2009
    Posts
    158
    Thanks
    30
    Thanked 0 Times in 0 Posts
    Steve,
    Thanks.

  12. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,198
    Thanks
    14
    Thanked 329 Times in 322 Posts
    You are very welcome.

    Steve

  13. #11
    2 Star Lounger
    Join Date
    Dec 2009
    Posts
    158
    Thanks
    30
    Thanked 0 Times in 0 Posts
    How can I copy the comment without the contents?

  14. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,198
    Thanks
    14
    Thanked 329 Times in 322 Posts
    I don't know what you mean exactly. Are you looking for a formula, macro, manual and what exactly do you want to do?

    Steve

  15. #13
    2 Star Lounger
    Join Date
    Dec 2009
    Posts
    158
    Thanks
    30
    Thanked 0 Times in 0 Posts
    Steve,
    Thank you for your continuing help.

    My original request was to copy cell [value] and comments.

    But I have 2 worksheets that have a few cells that are related but with different formulas and values. I need to carry the information from the source cell "comment" over to the destination cell in a similar comment [copy comment], but not the formula or value as I need to maintain the destination cells formulas/values. This would be the same as the original request without including the source cell value or formula, just the comment.

  16. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,198
    Thanks
    14
    Thanked 329 Times in 322 Posts
    Something like this perhaps? The cell contents will be the comment from the reference.

    Code:
    Option Explicit
    Public Function GetComment(Rng As Range) As Variant
      Dim sMsg As String
      Application.Volatile
      On Error Resume Next
      sMsg = Rng.Comment.Text
      On Error GoTo 0
      GetComment = sMsg
    End Function
    If a comment is edited, a recalc will be needed to update.
    If there is no comment then the function will return a null string.

    Steve

  17. #15
    2 Star Lounger
    Join Date
    Dec 2009
    Posts
    158
    Thanks
    30
    Thanked 0 Times in 0 Posts
    Steve,
    Thanks, but not what I need.
    A1[worksheet1] has a value of 5 and a comment. - I refer to this cell as the source cell.
    B2[worksheet2] has a value of 9[this may be a simple value or an output of a function or formula]. I refer to this cell as the destination cell. In this instance, for the comment only, not the value/formula.
    I need the comment from A1[worksheet1] to be "copied"? or added to B2[worksheet2] without affecting the value of B2[worksheet2].
    In the original solution, the value along with the comment of the source cell was copied to the destination cell, overriding the value of the destination cell. This works great when I need a "copy" of the cell, but in this instance, I need only the comment inserted/copied without overriding the value.
    Your last suggestion overrode the value of the destination cell with the contents of the comment.
    Hope this is clearer.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •