Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Adjusting Comment Printouts.

Adjusting Comment Printouts

Written by Allen Wyatt (last updated March 13, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


9

If you use comments in your worksheets quite a bit, you may wonder if there is a way to print the comments, but without the name and colon that normally preface every comment. Unfortunately, there is no built-in way to accomplish this in Excel. However, a macro can be used to quickly pull all the comments from a worksheet and place them in their own worksheet. This worksheet could then be printed, as it would amount to a compendium of all the comments. The macro is as follows:

Sub Workbook_BeforePrint(Cancel As Boolean)
    myCount = 0
    For Each c In ActiveSheet.Comments
        myCount = myCount + 1
        myComment = ActiveSheet.Comments(myCount).Text
        Sheets(2).Range("a1").Offset(myCount, 0).Value = _
          Mid(myComment,InStr(myComment, Chr(10)))
    Next
End Sub

This macro places the comments on the second worksheet in a workbook, so if you want them on a different worksheet (so you don't overwrite information already on the second sheet) you will have to make a modification to the Sheets(2) object.

Note, as well, that the macro name is Workbook_BeforePrint. This means that the macro will run every time you go to print your worksheet.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9602) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Adjusting Comment Printouts.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Stopping Styles from Changing with Multiple Users

Using styles in your documents can be very helpful when it comes to consistency and ease of formatting. When others open ...

Discover More

Understanding Justification

Paragraphs can be aligned in four different ways. This tip examines those alignment methods.

Discover More

Displaying the First Worksheet in a Macro

When creating macros, you often have to know how to display individual worksheets. VBA provides several ways you can ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Changing the Comment Font

When you add a comment to a worksheet, Excel uses a default font and size for the text. If you want to make changes to ...

Discover More

Copying Comments to Cells

Need to copy whatever is in a comment (a "note") into a cell on your worksheet? If you have lots of comments, manually ...

Discover More

Anchoring Comment Boxes in Desired Locations

Want your comment boxes to appear someplace other than the right side of a cell? You may be out of luck, and here's why.

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three less than 3?

2024-03-18 16:55:35

Tomek

@J. Woolley
Re: Microsoft should fire whoever applied the old name (Comment) [...]

Agreed!!! If it was a joke (as you joked earlier) it was a very bad one. Unluckily the damage is done and reverting to what it should be, would just cause more confusion and incompatibility.

May be "Notes" can be renamed "Remarks", and "Comments (Threaded)" - "Discussion" to avoid the term comments altogether. If it is done right, there may be a way to keep these objects still working with older workbooks. In VBA, Comments and CommentsThreaded should remain but new **aliases** for those could be "Remarks"/"Remark" and "Discussion"/"DiscussionItem" to make them work either way.

Just my dwa grosze (two pennies) worth.


2024-03-17 11:23:32

J. Woolley

My Excel Toolbox includes the following macros for legacy unthreaded comments (Notes):
CommentAuthors -- Remove author's name
FormatComments -- Format text, shape, color, and shadow
MoveComments -- Reposition near cell
ResizeComments -- Resize to fit text
UpdateComments -- Run each of the macros above
CopyToComments -- Copy source cell values to target cell comments
CommentDisplayMode -- Cycle display mode (none, indicator, comment)
CommentHyperlink -- Attach a hyperlink
PasteImageInComment -- Paste background image from clipboard or file

The following function will add a background image from a file to an unthreaded comment (Note):
=ImageInComment(ImageFile, [Target], [ScaleFactor], [RotateAngle], [NoAuthor])

The following functions apply to threaded comments (Comments) or unthreaded comments (Notes):
=CommentText(Target, [SkipAuthor]) -- Return the comment's text
=HasComment([Target], [Threaded], [AllCells]) -- True if Target has comment
=ListComments([AllSheets], [Threaded], [SkipHeader]) -- List comment info

Microsoft should fire whoever applied the old name (Comment) to the new threaded feature and gave the old unthreaded feature a new name (Note). At least they left the old VBA alone.
See https://sites.google.com/view/MyExcelToolbox


2024-03-17 01:58:28

Tomek

Note that macro from the tip does not provide information about the cell to which the comment is applied. Neither does the macro posted by J. Woolley on 2024-03-14 16:12:56. This may not be very useful for a large sheet with many comments.
The other macro J. Woolley posted on 2024-03-15 16:45:39 provides all this information. It can be easily modified to eliminate the authors' names, but it may be easier to just delete the column with this information from the resulting sheet.

I would probably opt not to use "BeforePrint" event, but create regular macros to be run on demand, then print the resulting sheets. After all, I use comments/noted mainly as tools when developing the spreadsheet, and often do not need them in final printout, if any. On the other hand they may guide users of the spreadsheet when entering the data, and would fulfill such role as pop-ups when the user hovers over the relevant cell.


2024-03-17 01:24:59

Tomek

Another approach to get the printout of comments/notes is to ask for it. To do this, in the Page Setup dialog box, under Sheet tab there is an option for comments to be printed "At the end of sheet". When printing, the comments and notes will be printed starting on the next page after the sheet.
You can select to print only the pages with comments. You can print to paper or to a pdf file.

If you have several sheets, each will have a section added to the printout with just comments/notes.

The printout will only have comments/notes that are within selection on the sheet(s) that is to be printed.

This approach does not remove the comment authors' names though. On the other hand it does not require a macro approach.


2024-03-15 16:45:39

J. Woolley

Here is a macro to record one row for each legacy unthreaded comment (Note) found in each of the workbook's worksheets. The results are saved in a worksheet named Comments with the following columns: Worksheet, Cell, Author, Comment (text). If the Comments worksheet does not already exist it will be added as the workbook's last sheet.

Public Sub RecordComments()
'record legacy unthreaded comments (Notes) in the Comments worksheet
    Dim n As Integer, i As Integer, c As Comment, s As String
    Dim MyWS As Worksheet, w As Worksheet
    Const MyWS_Name As String = "Comments"
    On Error Resume Next
        Set MyWS = Worksheets(MyWS_Name)
        If Err Then
            Err.Clear
            Set MyWS = Worksheets.Add(After:=Sheets(Sheets.Count))
            If Err Then MsgBox Err.Description, vbCritical: Exit Sub
            MyWS.Name = MyWS_Name
        End If
    On Error GoTo 0
    n = 1 'header row
    With MyWS
        .Cells(1).CurrentRegion.Clear
        .Cells(n, 1).Value = "Worksheet"
        .Cells(n, 2).Value = "Cell"
        .Cells(n, 3).Value = "Author"
        .Cells(n, 4).Value = "Comment"
        For Each w In Worksheets
            For Each c In w.Comments
                n = n + 1
                .Cells(n, 1).Value = w.Name
                .Cells(n, 2).Value = c.Parent.Address
                s = c.Author
                .Cells(n, 3).Value = s
                i = Len(s) + 2
                If Left(c.Text, i) <> (s & ":" & vbLf) Then i = 0
                .Cells(n, 4).Value = Mid(c.Text, (i + 1))
            Next c
        Next w
        With .Cells(1).CurrentRegion
            .Columns.VerticalAlignment = xlTop
            .Columns.ColumnWidth = 120
            .Columns.AutoFit
            .Rows.AutoFit
        End With
    End With
End Sub

The RecordComments macro should be located in a standard module. To update the results before printing the workbook, add the following event procedure to the ThisWorkbook module.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim activ As Object, selec As Object
    Set activ = ActiveSheet
    Set selec = ActiveWindow.SelectedSheets
    activ.Select
    RecordComments
    selec.Select
    activ.Activate
End Sub


2024-03-14 16:12:56

J. Woolley

@Barry
You could simply eliminate Option Explicit to make all variables Variant.
On the other hand, you could use this:
    Dim myCount As Integer, c As Comment, myComment As String
On the other hand, here is an improved version of the Tip's event procedure:

Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim n As Integer, i As Integer, c As Comment, s As String
    n = 1
    With Sheets(2)
        .Cells(n, 1).Value = "Comment Text"
        For Each c In ActiveSheet.Comments
            n = n + 1
            s = c.Text
            i = InStr(s, c.Author & ":" & Chr(10))
            If i > 0 Then i = InStr(s, Chr(10))
            .Cells(n, 1).Value = Mid(s, i + 1)
        Next c
    End With
End Sub

This version removes the comment's author only if it is present and does not include a blank line at the beginning of each comment. The Tip failed to mention that this event procedure must be located in the ThisWorkbook module; it is not an ordinary macro.
On the other hand, My Excel Toolbox includes the following dynamic array function:
    =ListComments([AllSheets], [Threaded], [SkipHeader])
This function returns one row for each comment with the following columns: Worksheet, Cell, Author, Comment (text). It works with threaded or unthreaded comments (Comments or Notes). When using pre-2021 versions of Excel without support for dynamic arrays, review the PDF file UseSpillArray.pdf.
See https://sites.google.com/view/MyExcelToolbox


2024-03-14 15:44:42

J. Woolley

@Hazel Kohler
In newer versions of Excel, a Note is VBA's Comment object and a Comment is VBA's CommentThreaded object. (Microsoft's little joke, I guess.) So the Tip's macro works with Notes.


2024-03-13 13:10:20

Barry

Hi.
I could really make a lot of use of the above macro. However I think it may be incomplete and I'm not sure how to rectify...

I have added:
Dim myCount As Integer
Dim c As Variant 'not sure what type to use
Dim myComment As Comment

When I run this I get an error:
Run time error 91
Object variable... not set

An help will be much appreciated


2024-03-13 06:01:19

Hazel Kohler

Would this also work for for Notes?


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.