Print comments "as displayed on sheet" macro

[Xx7]

Would like to create a macro that selects print comments "as displayed on sheet" for the entire workbook. I don't actually want the macro to print... but I want to toggle the comments to "as displayed on sheet", so that when I do print, they will be displayed. Thx!

 

[JPM]

Try this:

Sub Macro1()

Application.DisplayCommentIndicator = xlCommentAndIndicator
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.PrintComments = xlPrintInPlace
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
End Sub

 

[Xx7]

Hmmm... no luck with Excel 2010. When I run it it hides all my comments on my worksheet, so I can't see them . Then when I go to print, they don't show up either.

I should have been more clear... I have a worksheet with a bunch of comments that are already being shown (eg. I can read them on the worksheets without scrolling over them). I want to run a macro that doesn't do anything visibly to the workbook... but will print the comments as displayed on sheet.

 

[JPM]

OK, if your comments are already showing then adjust the macro to:

Sub Macro1()
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.PrintComments = xlPrintInPlace
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

However, I am using Excel 2007, so I don't know how this will work for you.

 

[Xx7]

It works to print the comments... however, periferated lines show up on the worksheet indicating the print area. Is there anyway to get rid of these? Maybe format the page setup to "Fit to one sheet" in the macro? thx!