Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Placing Formula Results in a Comment.
by Allen Wyatt
(last updated October 16, 2017)
Bob asked if it is possible to write a formula and get the answer 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 the 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 (13038) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Placing Formula Results in a Comment.
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!
Excel allows you to not only put information into cells, but into comments attached to those cells. Here's how to copy ...Discover More
Need to copy whatever is in a comment into a cell on your worksheet? If you have lots of comments, manually doing this ...Discover More
There are three different ways that Excel allows you to display any comments that are in your worksheet. Here's how you ...Discover More
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.