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.
The other option is to print them 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.
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.
There is also sample code creating a numbered list of comments on a separate sheet. Thanks to Dave Peterson for writing that!
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.
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.
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.