Excel.Tips.Net ExcelTips (Menu Interface)

Placing Formula Results in a Comment

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.

<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
                On Error GoTo 0
                .Comment.Text Text:=CStr(rCell.Value)
            End If
        End With
    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

        With Range("F15")
            .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)
        x = Target.Text
    End If

    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.

Related Tips:

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

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!)

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.