If you add comments to an Excel worksheet, you might want to include those comments when printing. There are a couple of built in options for printing comments, but neither is ideal. We'll look at those, then a numbering system, that's similar to numbered footnotes.
The Built In Options
In the Page Setup dialog box, on the Sheet tab, there are 3 options for printing the comments:
- (None)
- At end of sheet
- As displayed on sheet
If you select At end of sheet, a separate page of comments prints, listing the cell address, commenter name and comment text.
If you select As displayed on sheet, the comment that are currently visible on the worksheet will print, exactly as they appear on screen. That might work if there are a couple of comments that you want to show, and can arrange them over an empty space. Otherwise, you'll end up with a jumbled mess of comments, covering your data.
Add Numbers to Cells With Comments
Instead of using either of the built in options to print comments, you could use a bit of programming to add a tiny number at the top right of each cell that has a comment. Here's a close up view of the numbered cells.
List the Numbered Comments
With another bit of programming, you can create a numbered list of the comments, with other details, such as range name, cell value, cell address and comment text.
This list is on a separate worksheet, that you can print when you print the sheet with comments.
Download the Sample File
For Excel 2003, here's the link to Number and List Comments. There's sample code to add numbers, remove numbers and list the comments, and a zipped sample file that you can download.
That 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, you can download this sample file to Number and List Comments in Excel 2007.
Both files contain macros, so you may get a warning when you open them. Enable the macros if you want to run the code.
__________
I down loaded the 2007 version. The numbers in the squares do not correspond to the actual comment number as illustrated in your article. For example, comment one displays as comment 2, comment 2 as 3 and so on. In affect, the range of comment numbers are 2 to 6 instead of 1 to 5 (The actual number of comments.) This is a good tip and one I would like to use. Wnere did I go wrong?
Wayne, I'm not sure what the problem could be. I've uploaded a slightly different version of the 2007 file, that names the shapes, starting with "CmtNum", then deletes any shapes that have that prefix in their name. Thanks to Jon Peltier for that suggestion.
Could you download the "Number and List Comments in Excel 2007? file again? Then, in Excel 2007, click the Clear Numbers button, and try the Number Comments button.
Debra, I tried the 2007 version and it's numbering correctly for me. I suggest that you call RemoveIndicatorShapes at the begin of CoverCommentIndicator, kind of like deleting a menu before you create it. This way you don't have more that one shape for each comment. This is useful if you resize a column, which stretches the shape, and you want to renumber the comments with unstretched shapes in one swell foop.
Thanks Doug, and deleting the numbers at the start of the macro is a swell idea. ;-) I've revised the sample file to include that.