Adjusting Comment Printouts

@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.

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

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.

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.

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

@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

@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.

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

Would this also work for for Notes?