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:

>
> I would like to create a macro to get the commnets out of each cell and
> then add and display in another worksheet
>
>
> --
> shanelaura
> ------------------------------------------------------------------------
> shanelaura's Profile: http://www.excelforum.com/member.php...o&userid=33898
> View this thread: http://www.excelforum.com/showthread...hreadid=536733
>
>

[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 comment form but in text form if that makes sense

[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

"shanelaura" <[email protected]> wrote
in message news:[email protected]...
>
> 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 comment
> form but in text form if that makes sense
>
>
> --
> shanelaura
> ------------------------------------------------------------------------
> shanelaura's Profile:
http://www.excelforum.com/member.php...o&userid=33898
> View this thread: http://www.excelforum.com/showthread...hreadid=536733
>

[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

shanelaura wrote:
> 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 comment
> form but in text form if that makes sense
>
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

[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:

>
> 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 comment
> form but in text form if that makes sense
>
>
> --
> shanelaura
> ------------------------------------------------------------------------
> shanelaura's Profile: http://www.excelforum.com/member.php...o&userid=33898
> View this thread: http://www.excelforum.com/showthread...hreadid=536733
>
>

[Tom Ogilvy]

Here is something else you might be interested in:

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

A Chip Pearson's site.

--
Regards,
Tom Ogilvy



"shanelaura" wrote:

>
> 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 comment
> form but in text form if that makes sense
>
>
> --
> shanelaura
> ------------------------------------------------------------------------
> shanelaura's Profile: http://www.excelforum.com/member.php...o&userid=33898
> View this thread: http://www.excelforum.com/showthread...hreadid=536733
>
>