i've seen this before here yet the macro is hard to search

[driller]

Pertaining to *comments* on each cell

I got a basic problem of as follows

1) I need the comments to be auto-formatted using font size of 20.
2) I need to compile these comments regularly, since i need to revise them
too often, i need the records of 1st comment to revised comment.

Column A contains comments on most of its cells.

Is it possible to place record of these comments on a separate sheet so that
i can revised/update the comments from the original location after some time
? Historic compilation of comments.

I hope someone can give me the quicksearch on this macro done in this forum
before.

 

[Norman Jones]

Hi Driller,

to format the coments, see Debra Dalgleish
at:


Format All Comments
http://www.contextures.com/xlcomments03.html#Format

To keep an historic snapshot record of the
comments, see Debra Dalgleish at:

Copy Comments to Another Worksheet
http://www.contextures.com/xlcomments03.html#CopyToSheet

See also

Copy Comments to Microsoft Word
http://www.contextures.com/xlcomments03.html#CopyToWord

 

[driller]

Thanks Norman

It seems that Dave's macro will work for me.
Will it be possible to have minor adjustments on the code it a little
dynamic for my work.
-------
Sub showcomments()
'posted by Dave Peterson 2003-05-16
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("A11").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, 4).Value = mycell.Comment.Text
End With
Next mycell

Application.ScreenUpdating = True

End Sub
------------------------------
Can the *sheetname* of the added sheet on the workbook, with a list of
comments, contain the date (dd.mmm.yy) when the macro run.
Something like "newwks 11.jun.08" so I can re-run the Macro again on next
date.

Since there are rows to-be inserted at a time, can the cell address be
dynamic, ?
*newwks.Range("A11").Value = _
Array("Address", "Name", "Value", "Comment")*

thanks a lot
--
best regards,

Hi Driller,

to format the coments, see Debra Dalgleish
at:


Format All Comments
http://www.contextures.com/xlcomments03.html#Format

To keep an historic snapshot record of the
comments, see Debra Dalgleish at:

Copy Comments to Another Worksheet
http://www.contextures.com/xlcomments03.html#CopyToSheet

See also

Copy Comments to Microsoft Word
http://www.contextures.com/xlcomments03.html#CopyToWord