Display Comments in Spreadsheet (2000)

[dawn]

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?

[macropod]

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

[MichaelRead]

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>