Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Placing Formula Results in a Comment.

Placing Formula Results in a Comment

by Allen Wyatt
(last updated August 31, 2013)

<h1>Placing Formula Results in a Comment[3374]</h1> Excel won't allow you to directly or automatically insert the results of a formula into a cell's comment. You can, however, use a macro to place that result exactly where you want it.

Bob asked if it is possible to write a formula and get the result in a comment, instead of in a cell. The short answer is that no, you can't do it with a formula. You can, however, do it with a macro. For instance, the following macro adds the contents of two cells (A1 and B1) and then sticks the result in a comment attached to cell C1:

Sub MakeComment()
    With Worksheets(1).Range("C1").AddComment
        .Visible = True
        .Text "Total of cell A1 plus cell B1 is equal to " & _
          ([A1].Value) + ([B1].Value)
    End With
End Sub

If you'd rather run the macro on a range of cells, then a different approach is necessary. The following macro loops thru all the cells in a selection. If the cell contains a formula, the macro puts the value (the formula's result) in a comment attached to that cell.

Sub ValueToComment()
    Dim rCell As Range
    For Each rCell In Selection
        With rCell
            If .HasFormula Then
                On Error Resume Next
                .Comment.Delete
                On Error GoTo 0
                .AddComment
                .Comment.Text Text:=CStr(rCell.Value)
            End If
        End With
    Next
    Set rCell = Nothing
End Sub

While looping through the cells in the selection, if one of the cells has a formula and an existing comment, then the comment is deleted and replaced with the new comment that contains the formula result. Afterwards the cell's value will display as well as a comment with the same number. Instead of CStr you could also use Format function to display the value in any way you might want.

You can also create a macro that will modify a comment whenever you update the contents of a particular cell. For instance, let's say that every time someone made a change in cell C11, you wanted the result of whatever is in that cell to be placed into a comment attached to cell F15. The following macro does just that:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sResult As String

    If Union(Target, Range("C11")).Address = Target.Address Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        sResult = Target.Value
        Target.ClearContents

        With Range("F15")
            .ClearComments
            .AddComment
            .Comment.Text Text:=sResult
        End With
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

When someone enters a formula (or a value) into cell C11, the results of that formula (or the value itself) is placed into a comment that is attached to cell F15. Since this is an event-triggered macro, it needs to be entered in the code window for the worksheet on which it will function.

Finally, you may want to have your macro monitor an entire column. The following macro uses the Change event of a worksheet, just like the previous macro. It, however, only kicks into action if the change was made in column F, and only if a single cell in that column was changed.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Column <> 6 Then Exit Sub

    Dim x As String
    Application.EnableEvents = False
    If Target.HasFormula Then
        x = Evaluate(Target.Formula)
    Else
        x = Target.Text
    End If

    Target.ClearComments
    If Target.Text = "" Then
        Application.EnableEvents = True
        Exit Sub
    End If

    Target.AddComment x
    Target = ""
    Application.EnableEvents = True
End Sub

If the user makes a change to a single cell in column F, the macro grabs the result of what was entered and places it in a comment attached to that cell. The contents of the cell are then deleted.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3374) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Placing Formula Results in a Comment.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Modifying What is Started when You Start Windows

Did you know that Windows automatically starts extra programs whenever you boot your system? If you want to see which ...

Discover More

Changing Information in Multiple Documents

If you need to change text in many documents at the same time, Word isn't the best tool to use. Here's some ideas on ways you ...

Discover More

Field in Footer Won't Update

Word provides a handy shortcut that allows you to update the fields in any text you've selected. When you select your whole ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

MORE EXCELTIPS (MENU)

Using Named Formulas Across Workbooks

You can use the naming capabilities of Excel to name both ranges and formulas. Accessing that named information in a workbook ...

Discover More

Calculating Statistical Values on Different-Sized Subsets of Data

Discovering different ways to analyze your data can be a challenge. Here's how to work with arbitrary subsets of a large ...

Discover More

Pulling Formulas from a Worksheet

The formulas in your worksheet can be displayed (instead of formula results) by a simple configuration change. You can then ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share