Results 1 to 3 of 3
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Help to edit a UDF get collect comment text (Excel 2003)

    I have this code to extract text from a comment. It works fine, except that if my comment has paragraphs (hard returns), the text is concatenated at this point.
    For example: A comment that has this text...

    Rudi:
    This is my comments first line
    This is my comments second line

    is entered by the formula into the cell as:
    Rudi:This is my comments first lineThis is my comments second line

    Maybe we can get dashes between the paragraphs like:
    Rudi: - This is my comments first line - This is my comments second line

    The code I have is like this:
    Function GetCommentText(rCommentCell As Range)
    Dim strGotIt As String
    On Error Resume Next
    strGotIt = WorksheetFunction.Clean _
    (rCommentCell.Comment.Text)
    GetCommentText = strGotIt
    On Error GoTo 0
    End Function

    Any help here? TX
    Regards,
    Rudi

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

    Re: Help to edit a UDF get collect comment text (Excel 2003)

    The Clean function removes line ends (among other things). You coould use this instead:

    strGotIt = Replace(rCommentCell.Comment.Text, vbLf, " - ")

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help to edit a UDF get collect comment text (Excel 2003)

    Perfect...TX
    Regards,
    Rudi

Posting Permissions

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