Converting comments to data
 

Hi There,

I have a basic spreadsheet set up and in one particular column, each cell
has a comment attached. Is there some way that I create a new column and copy
each of those comments into the cells so that they become part of the data in
the spreadsheet? I don't want to cut and paste each individually as there are
hundreds of cells but do them on one go. Is this possible?

Thanks in advance for your help.

--
Cheers

GLR

Converting comments to data
 

Here is a very nice sample workbook:
http://www.contextures.on.ca/CommentsNumberPrint.zip

Tim


"glarosa" wrote:

Hi There,

I have a basic spreadsheet set up and in one particular column, each cell
has a comment attached. Is there some way that I create a new column and copy
each of those comments into the cells so that they become part of the data in
the spreadsheet? I don't want to cut and paste each individually as there are
hundreds of cells but do them on one go. Is this possible?

Thanks in advance for your help.

--
Cheers

GLR

Converting comments to data
 

Hi,

Right click your sheet tab, view code and paste this in. Currently it uses
Column A so change to suit. It will copy comments into the cell to the right
of the column you use and then delete th original comment.

Sub marine()
Application.ScreenUpdating = False
Set myrange = Range("A1:a100")
For Each c In myrange
On Error Resume Next
c.Offset(, 1).Value = c.Comment.Text
c.ClearComments
Next
Application.ScreenUpdating = True
End Sub

Mike

"glarosa" wrote:

Hi There,

I have a basic spreadsheet set up and in one particular column, each cell
has a comment attached. Is there some way that I create a new column and copy
each of those comments into the cells so that they become part of the data in
the spreadsheet? I don't want to cut and paste each individually as there are
hundreds of cells but do them on one go. Is this possible?

Thanks in advance for your help.

--
Cheers

GLR

Converting comments to data
 

Thanks Mike. I did as you said but I can't seem to get back to the worksheet
and activate the code. Sorry but I'm a very basic user. Can you explain
further?

Many Thanks
--
Cheers

GLR


"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in. Currently it uses
Column A so change to suit. It will copy comments into the cell to the right
of the column you use and then delete th original comment.

Sub marine()
Application.ScreenUpdating = False
Set myrange = Range("A1:a100")
For Each c In myrange
On Error Resume Next
c.Offset(, 1).Value = c.Comment.Text
c.ClearComments
Next
Application.ScreenUpdating = True
End Sub

Mike

"glarosa" wrote:

Hi There,

I have a basic spreadsheet set up and in one particular column, each cell
has a comment attached. Is there some way that I create a new column and copy
each of those comments into the cells so that they become part of the data in
the spreadsheet? I don't want to cut and paste each individually as there are
hundreds of cells but do them on one go. Is this possible?

Thanks in advance for your help.

--
Cheers

GLR

Converting comments to data
 

Thanks for your help Tim, but although I could open the spreadsheet, it would
not activate the macro because of the security level. Hence I could not gte
the thing to work.
--
Cheers

GLR


"Tim" wrote:


Here is a very nice sample workbook:
http://www.contextures.on.ca/CommentsNumberPrint.zip

Tim


"glarosa" wrote:

Hi There,

I have a basic spreadsheet set up and in one particular column, each cell
has a comment attached. Is there some way that I create a new column and copy
each of those comments into the cells so that they become part of the data in
the spreadsheet? I don't want to cut and paste each individually as there are
hundreds of cells but do them on one go. Is this possible?

Thanks in advance for your help.

--
Cheers

GLR

Converting comments to data
 

Hi,

First change your security level-ToolsOptionsSecurityMacro
SecurityChoose the €œLow€� option.

Then Copy this code:

Sub showcomments()
'posted by Dave Peterson 2003-05-16
Application.ScreenUpdating = False

Dim commrange As Range
Dim cmt As Comment
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("Number", "Name", "Value", "Comment")

i = 1
For Each cmt In curwks.Comments
With newwks
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = i - 1
.Cells(i, 2).Value = cmt.Parent.Name.Name
.Cells(i, 3).Value = cmt.Parent.Value
.Cells(i, 4).Value = cmt.Parent.Address
.Cells(i, 5).Value = Replace(cmt.Text, Chr(10), " ")
End With
Next cmt

newwks.Cells.WrapText = False
newwks.Columns.AutoFit

Application.ScreenUpdating = True

End Sub

Go to your workbookAlt+F11InsertModule and Paste the copied Code.
Close the VBA EditorSelect your worksheet with the commentsAlt+F8 and
Run the macro €œshowcomments€�.
After running the macro it will list all comments from your worksheet
regardless in which column are they on a new Worksheet. If you have comments
in more the one column and you insist to list only the comments from that
particular column then use the Mikes suggestion.
Hope this helps.

Tim


"glarosa" wrote:

Thanks Mike. I did as you said but I can't seem to get back to the worksheet
and activate the code. Sorry but I'm a very basic user. Can you explain
further?

Many Thanks
--
Cheers

GLR