Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dicplay Comment (XP)

    Just a quick one.

    Is there a way using a formula to capture the comment of one cell and display as the value of another cell?

    something like

    =COMMENT($A$5)

    to display the comment of A5 in the cell?
    Thanks,

    pmatz

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Dicplay Comment (XP)

    Add this to a module

    <pre>Function Comment(rng As Range)
    Comment = "No Comment"
    On Error Resume Next
    Comment = rng.Comment.Text
    End Function</pre>



    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dicplay Comment (XP)

    You can create a custom VBA function:

    Function GetComment(oCell As Range) As String
    On Error Resume Next
    GetComment = oCell.Comment.Text
    End Function

    and use it like this in a formula:

    =GetComment(A5)

    If you store the function in your personal macro workbook Personal.xls, use

    =Personal.xls!GetComment(A5)

  4. #4
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dicplay Comment (XP)

    A formula way to capture the comment of one cell :

    1] Assuming you have a Comment on cell A5

    2] Select Cell A1 >> Insert >> Name >> Define

    3] Defined workbook name : DisplayComment

    4] Refer to Box, enter formula :

    =GET.OBJECT(12, ROW(A1))

    5] Cell A1, enter the formula :

    =DisplayComment

    Regards
    Bosco

  5. #5
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dicplay Comment (XP)

    Hi,

    where do i put the code for a custom function? it doesnt work if I put it in the ThisWorkbook module. Do i need to add to an .xla?
    Thanks,

    pmatz

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dicplay Comment (XP)

    In a standard module, the type of module that you create by selecting Insert | Module in the Visual Basic Editor.

  7. #7
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display Comment (XP)

    Of Course... <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    Early monday morning comes to mind!!

    Thanks Hans
    Thanks,

    pmatz

Posting Permissions

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