Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to get comments out of a spreadsheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to get comments out of a spreadsheet
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to get comments out of a spreadsheet
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 -- shanelaur ----------------------------------------------------------------------- shanelaura's Profile: http://www.excelforum.com/member.php...fo&userid=3389 View this thread: http://www.excelforum.com/showthread.php?threadid=53673 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to get comments out of a spreadsheet
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to get comments out of a spreadsheet
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to get comments out of a spreadsheet
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to get comments out of a spreadsheet
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can Comments be printed in a spreadsheet? | Excel Discussion (Misc queries) | |||
Comments in a spreadsheet | New Users to Excel | |||
Include comments on another spreadsheet | Excel Discussion (Misc queries) | |||
printing spreadsheet comments | Setting up and Configuration of Excel | |||
Printing spreadsheet and comments | Excel Discussion (Misc queries) |