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.
Written by Allen Wyatt (last updated February 13, 2021)
This tip applies to Excel 97, 2000, 2002, and 2003
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.
Note:
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.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Your chosen occupation may require that you work with linear distances in feet and inches. Excel can do this, to a ...
Discover MoreIt is easy to use Excel functions to sum values based on criteria you establish, unless those criteria involve the ...
Discover MoreExcel is very good at counting things, even when those things need to meet specific criteria. This tip shows how you can ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-03-19 08:32:30
Barry
If its not specifically a Comment box that is needed a "Text Box" can reflect the contents of a cell.
Simply highlight the textbox and then in the formula bar type "=D2" (without quotes and whatever cell reference you want) and press Enter
2021-03-18 14:38:18
Christian Gerken
In the 3rd formula, how could the code be modified to enter line breaks between each comma? For instance, i would like the comment cell to show:
Apple
Prune
Kiwi
Orange
instead of:
Apple,Prune,Kiwi,Orange
Thank you!
2021-03-18 12:23:05
Christian Gerken
In the 3rd formula, how could the code be modified to enter line breaks between each comma? For instance, i would like the comment cell to show:
Apple
Prune
Kiwi
Orange
instead of:
Apple,Prune,Kiwi,Orange
Thank you!
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments