Excel 97 - Printing Comments with Cell Name

[Jim]

In Excel 97 when printing the comments at the end of the
document it prints 2 fields, cell name and comment.
However if I change the cell name from 'A1'
to 'Invoice_Number', 'A1' still prints out instead
of 'Invoice_Number'. Is there any way to get the custom
cell name to print out?

Thanks,

Jim

[Debra Dalgleish]

The following macro, written by Dave Peterson, will add a sheet to the
workbook, with a list of comments, including the cell address, and cell
name, if any:

Sub showcomments()
'by Dave Peterson
Application.ScreenUpdating = False

Dim commrange As Range
Dim mycell As Range
Dim curwks As Worksheet
Dim newwks As Worksheet
Dim i As Long

Set curwks = ActiveSheet

On Error Resume Next
Set commrange = curwks.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If commrange Is Nothing Then
MsgBox "no comments found"
Exit Sub
End If

Set newwks = Worksheets.Add

newwks.Range("a1:D1").Value = _
Array("Address", "Name", "Value", "Comment")

i = 1
For Each mycell In commrange
With newwks
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = mycell.Address
.Cells(i, 2).Value = mycell.Name.Name
.Cells(i, 3).Value = mycell.Value
.Cells(i, 3).NumberFormat = mycell.NumberFormat
.Cells(i, 4).Value = mycell.Comment.Text
End With
Next mycell

Application.ScreenUpdating = True

End Sub

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[Jim]

Yes, that worked. Just what I needed.

Thank you.

>.
>