Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell Comments (97 SR2)

    I am using Excel 97 SR2 on an NT system. 800 MHz Compaq with 512 Mb RAM.
    I would like to know how to use VBA to consolidate all cell comments found in a worksheet in a blank column of the same worksheet, one comment per cell continuing down until all comments are listed. I read through many posts and did searches on cell comments. I found Post 131532 by grahaml. The thread led me to the VBA code, found at Chip Pearson's site, that will put all the comments into a Word document.
    I prefer to have the comments listed down a column to the right of the data. The comment should start with the cell location followed by the actual comment. It is not necessary to include author information.
    I can do the code for the correct starting place (including the erasing of old comments), but I am not sure how to convert the macro to put the comments into cells in Excel instead of a Word document.
    In reading the posts, I am amazed at how fast and effective Woody
    Chuck Reimer
    I'm from the Government and I'm here to help...

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Comments (97 SR2)

    Is it a one-time deal, reimer?

    If so, all you'd need to do (sounds like), is select the text after it's in Word, hit Table-Convert text to table, copy/paste into Excel.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Cell Comments (97 SR2)

    The following code assumes the existence of an author name in the comment, and replaces it with the cell address. This address together with the actual comment text is then recorded in a a column starting at Z1 , which you can alter to meet to your requirements. <pre>Sub ListComments()
    Dim oCell As Range
    Dim lngCount As Long
    lngCount = 0
    For Each oCell In Cells.SpecialCells(xlCellTypeComments)
    Range("Z1").Offset(lngCount, 0) = _
    Replace(oCell.Comment.Text, oCell.Comment.Author & ":" & _
    vbLf, oCell.Address & " : ")
    lngCount = lngCount + 1
    Next
    End Sub</pre>

    All the above code works on the active worksheet.

    Andrew C

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Comments (97 SR2)

    I should have been more clear. It is for a co-worker and his style is such that it would not be a one-time, but something he would be updating many times throughout the day and would want the comment list updated.
    I have been working on changing the macro that puts the comments in Word and I am finding that it gathers all the coments for the entire Workbook. My preference would be to restrict it to the sheet the macro run from.
    Thanks for your fast responce.
    Chuck Reimer
    I'm from the Government and I'm here to help...

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Cell Comments (97 SR2)

    Sorry Reimer,

    but too late I noticed you are using XL97, which will cause problems with Replace, as this function is not supported in VBA for office 97. Add the following code as a seperate function, and change the word Replace in the code above to udfReplace.<pre>Private Function udfReplace(strOrigin As String, strFind As String, strReplace As String)
    Dim lPos As Long, lPrevPos As Long, iFindLen As Integer
    lPos = InStr(1, strOrigin, strFind)
    iFindLen = Len(strFind)
    lPrevPos = lPos
    Do Until lPos = 0
    strOrigin = Left(strOrigin, lPos - 1) & strReplace & Right _
    (strOrigin, Len(strOrigin) - (lPos - 1 + iFindLen))
    lPos = InStr(lPrevPos + Len(strReplace), strOrigin, strFind)
    lPrevPos = lPos
    Loop
    udfReplace = strOrigin
    End Function</pre>

    Andrew C

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Cell Comments (97 SR2)

    Here's an alternative macro that will allow your friend to nominate the range to extract the comments from. As requested, the macro discards the author's name. This macro prompt for the range of cells to extract the comments from and inserts them on the same row, 5 columns to the right of the selection
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Comments (97 SR2)

    Many thanks to everybody that took time to reply.
    Thanks to:
    Dreamboat - that would be a great solution for a one time project.
    Andrew Cronnolly - I had trouble getting it to work until I got you second post. Now it works GREAT!
    Macropod - It works great -but there are multiple cell comments on the same row. He needs them listed down a column.

    Thank you all for the effort. I now have a solution. <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>
    Chuck Reimer
    I'm from the Government and I'm here to help...

  8. #8
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Comments (97 SR2)

    Thanks folks! You've solved my problem, too!!


    All I wanted to do was get the cell address of the cells with comments in them. You'd have thought that Microsoft might have put it in the Comments collection or the Comment property (which is the only thing help talks about in relation to comments), but no <img src=/S/nope.gif border=0 alt=nope width=15 height=15>

    But if I use that Cells.SpecialCells(xlCellTypeComments) thing, I can get at the row and column of all the comments. Hurrah! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    At this rate, I might have some hair left by the time I've finished!!! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

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