Excel Hacks Ebook [Electronic resources]

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.

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 2-11.

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 Figure 2-12 and choose a shadow setting for the cell comment.

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