+ Reply to Thread
Results 1 to 3 of 3

How do I reference the comments of a cell?

  1. #1
    BrotherSun
    Guest

    How do I reference the comments of a cell?

    Can I reference the comments of a cell in a formula?

    For example, in cell B1, I want to type something like =A1.Comment

  2. #2
    Ardus Petus
    Guest

    Re: How do I reference the comments of a cell?

    dim sText as string
    sText=Range("A1").Comment.Text

    HTH
    --
    AP

    "BrotherSun" <BrotherSun@discussions.microsoft.com> a écrit dans le message
    de news: 9099F5C8-232E-4810-ABA6-EC055BCB36CA@microsoft.com...
    > Can I reference the comments of a cell in a formula?
    >
    > For example, in cell B1, I want to type something like =A1.Comment




  3. #3
    Ken Johnson
    Guest

    Re: How do I reference the comments of a cell?

    Hi BrotherSun,

    Paste this User Defined Function into a standard module in the VBA
    Editor of your workbook...

    Public Function CELCOM(rgCELL As Range) As String
    On Error GoTo NO_COMMENT
    CELCOM = "Comment in " & _
    rgCELL.Address(False, False) & _
    ":= " & _
    rgCELL.Comment.Text
    NO_COMMENT:
    End Function

    Example, say A1 has the comment "Have a nice day" and in Z1 you
    want to view A1's comment, then, in Z1 type..

    =CELCOM(A1)

    After pressing Enter Z1 will contain...

    Comment in A1:= Have a nice day

    You can insert the function either by typing it in or by going
    Insert>Function then on the Paste Function dialog select the All
    category then the CELCOM function.

    The function is not volatile, ie if the function is already in place in
    a cell and the cell comment it refers to is changed, it will not
    automatically update. So, to ensure that the CELCOM function returns
    current comment, Press F9 to force the workbook to calculate.

    Also, if the comment it refers to is multi-lined and you want the
    CELCOM function result to multi-lined then you will have to format the
    cell with the CELCOM function to have Text Wrapping.

    If you are unsure as to how to get the function code in place, then...

    1. Copy the code
    2. Press Alt + F11 to get to the VBA Editor
    3. Go Insert>Module then paste the code into that module.
    4. Press Alt + F11 to return to the worksheet.

    Ken Johnson


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1