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

Automatically Selecting Words

When editing a document, Word normally selects entire words as you use the mouse to select text. This tip explains why ...

Discover More

Using the REPT Function

Excel includes a handy function that allows you to repeat characters or strings of characters. How you use the REPT ...

Discover More

Creating Many Index Entries at Once

Word makes it fairly easy to create an index. It is creating the index entries that can be tedious, especially if you ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Pasting Pictures into a Comment

Excel allows you to use a picture as a background on a cell comment. This tip looks at how you can paste pictures into a ...

Discover More

Recognizing Notes and Comments in a Macro

When using macros to process comments, it is best to know the various ways that those comments can be accessed. This tip ...

Discover More

Changing Comment Color for a Single User

The default color used to format comments is determined by Windows, not Excel. You can adjust the formatting of ...

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 two more than 7?

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.