Macro to get comments out of a spreadsheet

[shanelaura]

I would like to create a macro to get the commnets out of each cell and
then add and display in another worksheet

 

[Stefi]

Record as a macro the following steps:
Select all cells
Copy
Select new sheet
Pastespecial Comments

Regards,
Stefi


„shanelaura†ezt írta:

 

[shanelaura]

Thank you for the reply
Not quite the result I'm looking for
I would like to see the comments added to a blank spread not in commen
form but in text form if that makes sens

 

[NickHK]

shanelaura,

Private Sub CommandButton1_Click()
Dim rng As Range
Dim i As Long

For Each rng In Selection
If Not rng.Comment Is Nothing Then
ThisWorkbook.Worksheets(2).Range("A1").Offset(i, 0).Value =
rng.Comment.Text
i = i + 1
End If
Next

End Sub

NickHK

 

[Andy Pope]

Hi,

Here is some code to get you started.

Sub TOC_Comments()

Dim rngCmt As Range
Dim rngCmts As Range
Dim rngOutput As Range

Set rngCmts = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)
Set rngOutput = Worksheets.Add.Range("A1")

For Each rngCmt In rngCmts.Cells
rngOutput = "'" & rngCmt.Parent.Name & "'!" & rngCmt.Address
rngOutput.Offset(0, 1) = rngCmt.Comment.Text
Set rngOutput = rngOutput.Offset(1, 0)
Next

End Sub

Cheers
Andy

 

[Stefi]

Sorry, I misunderstood your request. This UDF will display comments in source
sheet (Sheet1) as cell contents in destination sheet (Sheet2):

Function CommText(sourcesheet, cellrow, cellcol)
If Worksheets(sourcesheet).Cells(cellrow, cellcol).Comment Is Nothing Then
CommText = ""
Else
CommText = Worksheets(sourcesheet).Cells(cellrow,
cellcol).Comment.Text
End If
End Function

Enter in A1 in Sheet2
=CommText("Sheet1",ROW(),COLUMN())

and fill it right and down as required!

Regards,
Stefi


„shanelaura†ezt írta:

 

[Tom Ogilvy]

Here is something else you might be interested in:

http://www.cpearson.com/excel/excelM.htm#PrintComments

A Chip Pearson's site.