Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2001
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display Comments in Spreadsheet (2000)

    I can add comments to a cell in Excel. I can go into Tools/Options/View tab and turn on the Comments and Indicator option. I can print the comments after the spreadsheet. I want to have Excel put the indicator in the cell and display the comments below my spreadsheet. Essentially it would be the "extra" pages that print out when I print my Excel spreadsheet. I can't find any option to do this. Any ideas?

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

    Re: Display Comments in Spreadsheet (2000)

    Hi Dawn,

    The following macro should help you achieve what you're after. It reads each cell in a selected range and copies any comments it finds (including the author's name, if shown in the comment field) to a cell 30 rows below, along with the address of the source cell.

    If you don't want the source cell reference, delete "Cell.Address & ": " &" from the macro.
    You may also need to change the "Cell.Offset(30, 0)" statement to suit your needs - changing the 30 changes the number of rows before the comments appear, whilst changing the 0 changes the number of columns.
    If you want to get rid of the comment author names, change the "Cell.Comment.Text" to "Mid(Cell.Comment.Text, Len(Cell.Comment.Author) + 3)".

    Cheers

    Sub CopyCommentsToCells()
    On Error Resume Next
    For Each Cell In Selection
    Cell.Offset(30, 0) = Cell.Address & ": " & Cell.Comment.Text
    Next Cell
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display Comments in Spreadsheet (2000)

    Or if you ever got iced in and had nothing at all better to do, you could write them to a comma delimited text file for some unknown future purpose...

    <pre>Sub CommentTextFile()
    Dim fs, a
    Dim b As Integer
    Dim cell As Range
    Dim apple As String
    Dim banana As String
    Dim peach As String
    Application.ScreenUpdating = False
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.createtextfile(Left(ThisWorkbook.FullName, _
    Len(ThisWorkbook.FullName) - 4) & ".txt")
    With ActiveSheet
    For b = 1 To .Comments.Count
    apple = .Comments([img]/forums/images/smilies/cool.gif[/img].Parent.Address
    peach = Right(.Comments([img]/forums/images/smilies/cool.gif[/img].Text, Len(.Comments([img]/forums/images/smilies/cool.gif[/img].Text) _
    - Len(.Comments([img]/forums/images/smilies/cool.gif[/img].Author))
    banana = .Comments([img]/forums/images/smilies/cool.gif[/img].Author
    a.writeline (apple & "," & peach & "," & banana)
    Next
    End With
    a.Close
    Set fs = Nothing
    Set a = Nothing
    Application.ScreenUpdating = True
    End Sub
    </pre>


Posting Permissions

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