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.

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

Figure 2-12. 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/-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.