Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 271
Default Comments Boxes

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?


--
Thanks
Shawn
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22,906
Default Comments Boxes

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


On Fri, 8 Jan 2010 12:16:02 -0800, Shawn
wrote:

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?


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 50
Default Comments Boxes

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

--
* Please click Yes if this was helpful *
Andy Smith
Senior Systems Analyst
Standard & Poor''s, NYC



"Shawn" wrote:

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?


--
Thanks
Shawn

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default Comments Boxes

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

--
Rick (MVP - Excel)


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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


On Fri, 8 Jan 2010 12:16:02 -0800, Shawn
wrote:

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?



  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22,906
Default Comments Boxes

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

On Fri, 8 Jan 2010 16:35:11 -0500, "Rick Rothstein"
wrote:

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




  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 489
Default Comments Boxes

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
--
Cheers,
Ryan


"Shawn" wrote:

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?


--
Thanks
Shawn

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 271
Default Comments Boxes

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.
--
Thanks
Shawn


"Andy Smith" wrote:

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

--
* Please click Yes if this was helpful *
Andy Smith
Senior Systems Analyst
Standard & Poor''s, NYC



"Shawn" wrote:

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?


--
Thanks
Shawn

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
hidden comments boxes Shawn Excel Programming 1 December 21st 09 10:50 PM
Comments boxes resizing HeatherBelle Excel Discussion (Misc queries) 3 December 2nd 08 07:29 PM
Comments Boxes bluebadger Excel Discussion (Misc queries) 1 May 16th 06 12:58 PM
Dynamic Comments/Validation/Text Boxes George Andrews Excel Programming 0 May 16th 05 10:20 PM
Resizing "comments" boxes in a workbook all at once rita Excel Discussion (Misc queries) 2 February 18th 05 02:41 AM


All times are GMT +1. The time now is 01:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"