Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hidden comments boxes | Excel Programming | |||
Comments boxes resizing | Excel Discussion (Misc queries) | |||
Comments Boxes | Excel Discussion (Misc queries) | |||
Dynamic Comments/Validation/Text Boxes | Excel Programming | |||
Resizing "comments" boxes in a workbook all at once | Excel Discussion (Misc queries) |