copying comments in excel to a separate worksheet

[dmars]

Hi,

Can anyone help me with the code needed to copy all my comments to another
worksheet(history log) in the same workbook? Comments will need to be
appended to the worksheet as they are added. I was able to find code that
would copy all coments to a new sheet but everytime I run it it creates a new
sheet and replaces the file. It doesn't add to the existing worksheet. I am
trying to create a history log of all comments. When a comment is deleted
from the spreadsheet it should remain on the history log and if a comment is
added it should append to the worksheet.

Thanks for your help.

 

[Mike H]

Have a look here

http://www.contextures.com/xlcomments03.html#CopyToSheet

Mike

 

[Gary''s Student]

Give this a try:

Sub copycomment()
'gsnuxx
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Activate
Set cRange = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments)
If s2.Comments.Count = 0 Then
For Each r In cRange
ad = r.Address
r.Copy
s2.Range(r.Address).PasteSpecial Paste:=xlPasteComments
Next
Else
Set c = s2.UsedRange.SpecialCells(xlCellTypeComments)
For Each r In cRange
ad = r.Address
If Intersect(s2.Range(ad), c) Is Nothing Then
r.Copy
s2.Range(r.Address).PasteSpecial Paste:=xlPasteComments
End If
Next
End If
End Sub

First it gets the range of all comments on the first sheet. If there are no
comments on the second sheet, the comments just get copied over.

If there are comments on the second sheet, then care is taken not to
over-write them.

This means that old comments already existing on Sheet2 will be preserved.

 

[dmars]

Mike thanks for getting to me so quickly. I ran the macro and it works. The
only problem is that it creates a new sheet every time I run it. I need to
have one sheet with all comments. At some point I will delete the comments
in the worksheet but still need to have them archived in the history log. I
really don't know much about VBA so I appreciate your help. It would also be
helpful if I could get the date and file name to go into the history log. At
the end of every month I version up the workbook and delete the comments for
that month but the history log should maintain all comments and allow me to
add to them.

Thanks again.

 

[Mike H]

Hi,

the code will now look for a sheet called "Comments" and copy all the
comments to that and add the date/time and the workbook name

Sub ShowCommentsAllSheets()
'modified from code
'posted by Dave Peterson 2003-05-16
Application.ScreenUpdating = False

Dim commrange As Range
Dim mycell As Range
Dim ws As Worksheet
Dim newwks As Worksheet
Dim i As Long

Set newwks = Sheets("comments")
If newwks.Range("a1") = "" Then
newwks.Range("A1:G1").Value = _
Array("Sheet", "Address", "Name", "Value", "Comment", "Date", "Workbook")
End If

For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
Set commrange = ws.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If commrange Is Nothing Then
'do nothing
Else

i = newwks.Cells(Rows.Count, 1).End(xlUp).Row

For Each mycell In commrange
With newwks
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = ws.Name
.Cells(i, 2).Value = mycell.Address
.Cells(i, 3).Value = mycell.Name.Name
.Cells(i, 4).Value = mycell.Value
.Cells(i, 5).Value = mycell.Comment.Text
.Cells(i, 6).Value = Now()
.Cells(i, 7).Value = ActiveWorkbook.Name
End With
Next mycell
End If
Set commrange = Nothing
Next ws

'format cells for no wrapping, remove line break
newwks.Cells.WrapText = False
newwks.Columns("E:E").Replace What:=Chr(10), _
Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Application.ScreenUpdating = True

End SubMike

 

[dmars]

Wow! We are almost there. When i first ran the macro it copied all my
comments to the comments sheet. But when I ran the macro the second time to
capture any additional comments it duplicated all the comments. I ended up
with all comments entered twice. I only need to add or append any new
comments or changes to the comments to the comment sheet. You guys are
incredible. I can't tell you how much time you will save me.

 

[Dave Peterson]

Maybe you could just clear out any existing comments on that Comments sheet
before the real work starts.

Set newwks = Sheets("comments")
newwks.cells.clear '<-- added
If newwks.Range("a1") = "" Then

 

[dmars]

Hi thanks for the help but this maco copied the entire comment box not just
the text.
It did however allow me to append changes or new comments to the existing
sheet. I am trying to create a history log like this one:

Sheet Address Name Value Comment Workbook


You guys are the best. I appreciate all your help.

I may be getting greedy but I also want to add code to this macro that will
format the text in the comment box to be blue.

Sub CommentDateTimeAdd()
'adds comment with date and time,
' positions cursor at end of comment text
'www.contextures.com\xlcomments03.html

Dim strDate As String
Dim cmt As Comment

strDate = "dd-mmm-yy"""
Set cmt = ActiveCell.Comment

If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
cmt.Text Text:=Format(Now, strDate) & Chr(10)
Else
cmt.Text Text:=cmt.Text & Chr(10) _
& Format(Now, strDate) & Chr(10)
End If

With cmt.Shape.TextFrame
.Characters.Font.Bold = False
End With

SendKeys "%ie~"

End Sub

 

[Gary''s Student]

This will make your comments blue:

Sub BlueIt()
Dim cm As Comment
For Each cm In ActiveSheet.Comments
cm.Shape.TextFrame.Characters.Font.ColorIndex = 5
Next
End Sub

 

[dmars]

I'm sorry to keep bothering you but we are getting there. I added the line
of code to clear the worksheet each time I run the code and it works.
However, at some point after there are too many comments in the spreadsheet
and after they have been reviewed by mgmt I delete them and save the
spreadsheet with a different version number. If I delete the comments from
the requirements worksheet and then run the macro I have lost my previous
history log(comments worksheet) and I start over. Of course, I can probably
copy and paste it from the previous version but it would be simpler if this
macro would just allow me to keep appending to it. The comments sheet should
always retain all comments. I work in the pharmaceutical industry and we are
very regulated that is why I need to track any comments made to the training
requirements spreadsheet. If it is not possible to do this then I will just
retain the comments for each version separately.

Thanks again for your help with this.

 

[Dave Peterson]

If the comment cells don't change location--no insertion of rows/columns and no
deletion, either, then maybe you could check the address of the cell with the
comment.

If you find a match on the comment worksheet, you could either overwrite that
row (if the comment itself changed) or just skip that comment.

But if you added a comment to x99 (say) and deleted row 2, then x98 is the cell
with the comment. So this technique wouldn't work.

Personally, I'm not sure I'd use comments as the home for these type of notes.
I'd use extra columns in that row. And make sure that each row has a unique
identifier on it.

 

[dmars]

Thank you that was an easy one! This workbook was not created by me and it
should have been created in Access but i have to work with it. I have been
able to copy the comments to the history sheet and it works. The only
problem i am experiencing is that the macro i am using copies the comments
and doesn't really append them to the history log but i can work around that.
Thanks for all your help.

 

[dmars]

David,

thanks again for all your help. I think I can work around the issues. I am
not concerned about retaing the cell address of the comment just a running
log of all comments that have been entered or edited onthe requirements
worksheet. The macro could just keep appending to the bottom of the history
sheet. I will just make sure to save the history log in a separate workbook
or file to prevent it from being deleted as the user deletes his/her comments.

Thanks Again!

 

[dmars]

That was so easy. What if I have the intial comment in blue could I have the
responding comment(approver) in red using the same code below? Please let me
know if I am asking too much and if you are too busy with other requests. I
don't want to get a reputation as a pain in the neck and not have anyone want
to respond to me if I have any further questions down the road.

 

[Gary''s Student]

Feel free to ask any questions. Comments are an interesting topic. It is
easy to color-code comments according to the identity of the commenter.

As you have already noticed, Excel, by default, inserts the username into
the cell (it also saves the username in the mouse-over message.

You could write a macro that loops over the comments in a worksheet,
examining the user. If the comment text contains Baba Ghanoush, mark the
text red. If the text contains Natasha Fatale, mark the text blue. If the
text contains Victor Laszlo, mark the text green.


etc.