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.
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.
Learn more about Allen...
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.
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.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!