Comments Boxes

[Shawn]

My organization uses a time sheet program built in excel. The auditor
requires a "comments box" be added to note any changes made to the time sheet
after the employee signs it.

Sometimes there ends up being dozens of comments boxes all over the time
sheet. We have to print each one and file them at the administrative office.

My questions are, is there a way the comments can be printed on a seperate
sheet? If so, can it list not only the content of the comments box but the
address of the cell it referenced? Then, can it be coded so the comments
boxes don't show up when the paper version is printed?

 

[Gord Dibben]

This macro from Debra Dalgleish will give you separate sheet with all
Comments and Addresses for your records.

Sub ListComms()
Dim Cell As Range
Dim sh As Worksheet
Dim csh As Worksheet
Set csh = ActiveWorkbook.Worksheets.Add
csh.Name = "Comments"
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> csh.Name Then
For Each Cell In sh.UsedRange
If Not Cell.Comment Is Nothing Then
With csh.Range("a65536").End(xlUp).Offset(1, 0)
.Value = sh.Name & " " & Cell.Address
.Offset(0, 1).Value = Cell.Comment.text
End With
End If
Next Cell
End If
Next sh
End Sub

As far as not printing the Comments when printing sheets, page setup is
where you set Comments to print or not.


Gord Dibben MS Excel MVP

 

[Andy Smith]

Pull down File / Page Setup, go to the Sheet tab, and change Comments to At
End of Sheet.

 

[Rick Rothstein]

I think iterating each sheet's Comments collection would be more efficient
than iterating each cell in the UsedRange. Also the Range("A65536")
reference should be changed to Cells(Rows.Count,"A") so the code will work
in XL2007/2010 as well as earlier versions. I have saved as much of Debra's
original structure as possible while incorporating the above modifications
and this is the macros that I came up with...

Sub ListComments()
Dim C As Comment
Dim Sh As Worksheet
Dim CSh As Worksheet
Set CSh = ActiveWorkbook.Worksheets.Add
CSh.Name = "Comments"
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Name <> CSh.Name Then
For Each C In Sh.Comments
With CSh.Cells(CSh.Rows.Count, "A").End(xlUp).Offset(1, 0)
.Value = Sh.Name & " - " & C.Parent.Address
.Offset(0, 1).Value = C.Text
End With
Next
End If
Next
End Sub

 

[Gord Dibben]

Good points Rick

Much of the older routines we're used to are written for the 65536 rows and
need tuning for the newer versions of Excel.

Also looping through Comments only makes it more efficient.


Gord

 

[Ryan H]

This macro will work properly only if it is ran once per time sheet. If it
is run multiple times then you will have to let me know, because this code
will throw an error when it tries to create another worksheet named "Comments
Log". Also, I put the PrintOut method at the bottom of the code. Keep in
mind that it will use your current print settings. If you need to change
them each time, use this line of code instead, which will show the Print
Preview window and allow you to make changes.

' print sheet
wksComments.PrintOut Preview:=True

Hope this helps! If so, let me know, click "YES" below.

Sub ListComments()

Dim wksComments As Worksheet
Dim wks As Worksheet
Dim cmt As Comment
Dim InputRow As Long

' add new sheet at end of sheets
Set wksComments = Sheets.Add(After:=Sheets(Sheets.Count))
wksComments.Name = "Comments Log"

' find next available row
InputRow = wksComments.Cells(Rows.Count, "A").End(xlUp).Row + 1

' loop thru each sheet in workbook
For Each wks In Worksheets

' loop thru each comment in wks
For Each cmt In wks.Comments
With wksComments
' get sheet name of comment
.Cells(InputRow, "A").Value = wks.Name
' get cell address of comment
.Cells(InputRow, "B").Value = cmt.Parent.Address
' get comment text
.Cells(InputRow, "C").Value = cmt.Text
End With
InputRow = InputRow + 1
Next cmt

' don't allow comments to print on sheet
wks.PageSetup.PrintComments = xlPrintNoComments

Next wks

' print sheet
wksComments.PrintOut Copies:=1

End Sub

 

[Shawn]

I have used exel for 13 years and didn't know this feature existed. While
all the options in this string are helpful, this is the simpliest and just
what I need. I can't believe I didn't know this already.