O'Reilly Hacks
oreilly.comO'Reilly NetworkSafari BookshelfConferences Sign In/My Account | View Cart   
Book List Learning Lab PDFs O'Reilly Gear Newsletters Press Room Jobs  



HACK
#28
Customize Cell Comments
Cell comments enable you to place the electronic equivalent of a sticky note to any specified cell in a worksheet. Although many people use cell comments, many don't know that cell comments are customizable
[Discuss (1) | Link to this hack]

When you insert a cell comment via Insert → Comment, Excel will, by default, also insert the username for the PC being used. You can change this by selecting Tools → Options → General. The username will appear at the bottom of the dialog box that opens, where you can type whatever you want to be shown by default.

Although cell comments serve the simple purpose of displaying a message to either yourself or another user, you can customize the cell comment so that it better reflects your intentions.

Ensure that the Drawing toolbar is displayed by selecting View → Toolbars → Drawing. Insert a cell comment into a cell by selecting the cell and then selecting Insert → Comment. This automatically places you in Edit mode, ready to enter text into the comment box.

Left-click the outside border of the cell comment so that you are no longer in Edit mode. With the comment selected, select Draw from the Drawing toolbar, then Change AutoShape. You will be presented with a list of options including Basic Shapes, Block Arrow, Flow Chart, Stars and Banners, and Callouts. Choose an option, and the cell comment will change to the shape selected, as shown in .

Figure 1. A dramatically formatted cell comment

You can take this a step further by applying a shadow style to the comment to give it a more lifelike and three-dimensional look. Ensure that your comment is still selected, but that you are no longer in Edit mode. On the Drawing toolbar, click the Shadow Settings icon shown in and choose a shadow setting for the cell comment.

Figure 2. Shadow Settings options

Another interesting thing you can do with cell comments is use them to display pictures without impeding on any associated data. For instance, you could insert a picture of a chart into a cell comment to better illustrate the data in the chart without having to show the chart all the time.

To add a picture, ensure that the cell comment is selected, but that you are not in Edit mode. Left-click and select Format → Comment, or double-click the Comment border. Select Colors and Lines from the Format Comment dialog box. From the Color options, select Fill Effects, and from the Fill Effects dialog, select Picture. Now browse to the picture you want to insert into the cell comment.

One last thing you can do to cell comments is extract the text that was placed into a cell comment and have it appear in a cell. To do this, you need to place a simple custom function into a standard module. Select Tools → Macro → Visual Basic Editor (Alt/Option-F11), then select Insert → Module and enter the following code:

Function GetCommentText(rCommentCell As Range)
Dim strGotIt As String
    On Error Resume Next
    strGotIt = WorksheetFunction.Clean _
              (rCommentCell.Comment.Text)
    GetCommentText = strGotIt
    On Error GoTo 0
End Function

To return to Excel, either click the Close button or press Alt/&command;-Q. Now, in any cell, enter the following formula:

=GetCommentText(A1)

where A1 has a cell comment in it. The comment text will appear in the cell.


O'Reilly Home | Privacy Policy

© 2007 O'Reilly Media, Inc.
Website: | Customer Service: | Book issues:

All trademarks and registered trademarks appearing on oreilly.com are the property of their respective owners.