Print Numbered List of Excel Comments

In Excel, there are two built-in options for printing comments. The first choice is to print them at the end of the worksheet.

For that selection, all the worksheet comments are listed in a single column, with labels to the left, as shown below.

CommentPrintEnd

Print As Displayed

The other option is to print comments as displayed on the worksheet. Any comment that is currently visible on the worksheet will print, exactly as they appear on screen.

If you arrange things carefully, they’ll look okay, but with closely positioned comments things will look messy.

CommentPrintDisp

Print a Numbered List

On my Contextures website, there is code that lets you show a number at the top right of each comment cell. These little rectangles cover up the red triangle that marks the comment cells.

This is a zoomed in view of the numbers.

zoomed in view of comment numbers
zoomed in view of comment numbers

Macro to Print List

There is also sample code creating a numbered list of comments on a separate sheet. Thanks to Dave Peterson for writing that!

CommentPrintList

List Comments With Merged Cells

I avoid merged cells whenever possible, and hadn’t noticed that there was a problem listing comments that are in merged cells.

Someone contacted me last week, to see if there was a way to list those comment cells once, instead of listing all the cells in the merged area.

Here’s the list, using the old code. Cells A1:D1 are merged, and they’re all listed.

old macro - merged cell comment in numbered list
old macro – merged cell comment in numbered list

New Macro for Merged Cells

So, I’ve created a new procedure in the Excel 2007 file, and added a button to the worksheet. If your worksheet has merged cells with comments, use that button, for better results in the numbered list.

The code works with merged or normal cells, and also copies the number format from the source cell.

Here is the list created by the new code. There is only one listing for the merged cell, and the Tax cell shows the number formatted as a percentage.

new macro - merged cell comment in numbered list
new macro – merged cell comment in numbered list

Download the Sample File

To download the sample file for Excel 2003 or Excel 2007/2010, go to the Number and List Comments section on the Comments programming page.

There’s sample code to add numbers, remove numbers and list the comments, and a zipped sample file that you can download.

The Excel 2003 numbering code didn’t work well in Excel 2007. The numbers didn’t appear in some boxes, and the boxes didn’t line up correctly in the cells. So if you’re using Excel 2007, be sure to download that version’s sample file

Both files contain macros, so you may get a warning when you open them. Enable the macros if you want to run the code.
_____________________________

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.