Cell Comments 

Cell comments (cell notes) are comments that can be associated with a cell.  There is a little RED triangle than can be made to appear in the upper right-hand corner using Options, General.  You can print cell notes with your printed sheet by choosing cell notes option on File, Setup, Sheets and be sure to include print row and column headings or you will not be able to identify where the cell notes came from.

Cell comments can be shown all at once or hidden all at once with or without only their cell comment triangle showing in Tools, options, view.
    Comments:  _ None,   x Comment indicator only,   _ Comment & indicator

In addition individual Cell comments can be shown or hidden by right clicking on the cell with a cell comment and choosing Hide or Show Comment.  Cell comments that are shown can be reformatted and can be moved.  Hiding and unhiding columns may drastically affect position of a cell comment, but you can move it around by DClick on the cell comment outline.

RClick on a cell will show these options as applicable:  Insert Comment, Edit Comment, Delete Comment, Show Comment.

RClick on the cell comment itself has several options besides providing the ability to change font and to move the comment that inclues special options when multiple comments are shown: i.e. the ability to move the cell comment to front, move to back, move forward, move backward to determine which comment will appear on top of the others.

To show comments or not show all comments there is also
View menu --> View Comments

Insert menu, will show as applicable:  Insert comment, Edit Comment

Print Comments using Excel Options
You can print the comments when you print the workbook,
  File, Page Setup, Sheets,
    [x] Row and Column Headings
    Comments: At End of Sheet
If you do not printer row/column headings Excel will not indicate the cell that the comment refers to.

Print all cell comments to a file and view the results with your web browser.

Option Explicit
Sub writeComments()
' Nick Hodge -- 1999-11-13 in MISC, prints cell  comments out to a
'  text file, with their address.
'  http://groups.google.com/groups?oi=djq&ic=1&selm=an_548083890
' modified 1999-12-29 D.McRitchie to include cell to left of comment,
'  and browse result

Dim mycomment As Comment, filename As String
Dim mySht As Worksheet
Dim IEpath As String, Netscapepath As String
filename = "C:\temp\ccomment.txt"
Open filename For Output As #1
Print #1, FormatDateTime(Date, vbLongDate)
For Each mySht In Worksheets
    For Each mycomment In Worksheets(mySht.Name).Comments
      Print #1, " "
      Print #1, mycomment.Parent.Parent.Name & "!" _
        & mycomment.Parent.Address(0, 0) _
            & "  comment: " & Trim(mycomment.Text)
      If mycomment.Parent.Column > 1 Then _
         Print #1, "   cell " & mycomment.Parent.offset(0, -1). _
         Address(0, 0) & " on left has value: " _
         & mycomment.Parent.offset(0, -1).Value
      Print #1, "   cell " & mycomment.Parent.Address(0, 0) & _
         " has value: " & mycomment.Parent.Value
    Next mycomment
Next mySht
Close #1
Netscapepath = _
   "H:\program files\netscape\Communicator\program\netscape.exe"
IEpath = "C:\program files\internet explorer\iexplore.exe"
Shell IEpath & " " & filename, vbNormalFocus
'Shell Netscapepath & " " & filename, vbNormalFocus
'Shell "Notepad " & filename, vbNormalFocus
End Sub

XL2HTMLX (extended) version which adds the grey shaded Column and Row headers.  IExplorer is then invoked for a review and the example (coding) can be copied from view source into a permanent web page.  As the actual purpose is to generate as little code as possible you will have to right justify any cells including numeric cells that you want right justified in your HTML code.  Additional links to HTML conversions can be found on the homepage for My Excel Pages.

Function to obtain cell comments from another cell

Function to obtain cell comments from another cell, was posted by ...

Function MyComment(rng As Range)
    Application.Volatile
    Dim str As String
    str = Trim(rng.Comment.Text)
'// If you want to remove Chr(10) character from string, then
    str = Application.Substitute(str, vbLf, " ")
    MyComment = str
End Function

Comments were Notes prior to Excel 97.

Function to detect cell comments in another cell

Function HasComment(Target As Range) As Boolean
   'Patrick Molloy, programming 2001-11-17  
   On Error Resume Next    ' to use in WS:  =HasComment(a1)
   Dim txt As String       ' to use in VBA: MsgBox hascomment(Range("a1"))
    txt = Target.Comment.Text   'in Event: MsgBox hascomment(Target)
    HasComment = Err.number = 0
    Err.Clear
End Function

If used in a worksheet you would have to use Ctrl+alt+F9 to recalculate since this is quite naturally not marked as a Volatile Function.  Volatile Functions can really have a negative impact on performance.  In reality since Worksheet Comments usually are marked with red triangle there would be little use for this function in a worksheet anyway.

Macro to populate comments in a range with text values of another range

Macro posted by Dave Ramage to obtain cell comment values for a single range from the text values of another another matching single range (based on cell count of 1st range).  Modified to use TEXT value which is the the displayed value instead of value.

Sub AddComments()
'Posted by Dave Ramage, 2001-04-11, misc,
Dim rngComments, rngCells As Range
Dim lCnt As Long

'get user to select range
Set rngComments = Application.InputBox(prompt:="Select" _
   & "range containing comments text:", _
   Title:="Add comments: Step 1 of 2", Type:=8)
'was Cancel pressed?
If rngComments Is Nothing Then Exit Sub

Set rngCells = Application.InputBox(prompt:="Select cells to update:", _
                    Title:="Add comments: Step 2 of 2", _
                    Type:=8)
If rngCells Is Nothing Then Exit Sub

'are ranges the same size?
If rngCells.Areas(1).Cells.Count <> rngComments.Areas(1).Cells.Count Then
    MsgBox ("Ranges must be the same size!")
    Exit Sub
End If

'add comments
For lCnt = 1 To rngCells.Areas(1).Cells.Count
    'does the cell already have a comment?
    If rngCells.Areas(1).Cells(lCnt).Comment Is Nothing Then
        'no comment, so add one
        rngCells.Areas(1).Cells(lCnt).AddComment _
           rngComments.Areas(1).Cells(lCnt).Text
    Else
        'already comment, so delete then add
        rngCells.Areas(1).Cells(lCnt).Comment.Delete
        rngCells.Areas(1).Cells(lCnt).AddComment _
           rngComments.Areas(1).Cells(lCnt).Text
    End If
Next lCnt
End Sub

Function to check for presence of comments

Andrew Kirienko 1997-09-07

Public Function IsCommentsPresent() As Boolean
   IsCommentsPresent = ( ActiveSheet.Comments.Count <> 0 )
End Function

Adding / Changing Cell Comments

Pick one of the lines within.  See VBE Help you can also play with Visible (visibility).

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'to install -- rightclick on the sheettab of the sheet to
' be used in and choose 'view code'.  Paste this Worksheet
' event macro into the module.
   ActiveCell.AddComment.Text "Part Not Found"
   Target.Offset(0, 0).AddComment.Text "(Part Not Found)"
   Target.Offset(0, 0).Comment.Text "--- Part Not Found ---"
End Sub

Tracking single cell Changes to value

To track all changes see Hilight Change in the Related Area.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Column <> 5 Then Exit Sub
   If Target.Row <> 5 Then Exit Sub
      Dim ccc As String
   ccc = Format(Date + Time, "mm/dd/yy hh:mm") _
      & " " & Target.Value  ' -- Application.UserName
   If Target.Comment Is Nothing Then
      Target.AddComment.Text ccc
   Else
      Target.Comment.Text (Target.Comment.Text & Chr(10) & ccc)
   End If
   Target.Comment.Shape.TextFrame.AutoSize = True
End Sub

Changing Font etc in cell comments

You can manually edit a comment and RClick on it's frame to change things.  In control panel you can change Display, Appearance, ToolTips to make default changes for newly created cell comments.  The following macro is attributed to John Walkenbach to do the same in a macro for the active cell (selected).

Sub CommentChange()
  With ActiveCell
    .Comment.Shape.TextFrame. _
        Characters.Font.Size = 14
    .Comment.Shape.TextFrame. _
       Characters.Font.Bold = False 'or True
    .Comment.Shape.TextFrame. _
      Characters.Font.ColorIndex = 3 'Red
  End With
End Sub

Change fontsize of all cell comments

Sub ChgAllCommentsF14()
    Dim Cell As Range
    For Each Cell In Cells.SpecialCells(xlCellTypeComments)
        With Cell.Comment.Shape.TextFrame
            .Characters.Font.Name = "Terminal"
            .Characters.Font.Size = 14
            .AutoSize = True
        End With
        Cell.Comment.Shape.TextFrame.AutoSize = True
    Next
End Sub

Placing Formulas into Cell Comments

Placing the formula into a cell comment may not be very practical except for immediate viewing because no change will take place if the formula changes, and it would seem that the usual purpose of showing the formula is because they are being worked on or for documentation.  The only way this works for documentation is to print the comments, and it would be more effective to print the actual formulas. (see related area on my formula page).

Sub FormulasIntoComments()
    Dim cell As Range
    Selection.ClearComments
    For Each cell In Selection
      If cell.HasFormula Then
        cell.AddComment cell.Formula
        cell.Comment.Visible = False
        cell.Comment.Shape.TextFrame.AutoSize = True
      End If
    Next cell
End Sub

My own preference is to show the formulas actually in use on the right on the same page as the formulas are active on.  See GetFormula on my formula page.

Placing Displayed Text into Cell Comments

The following does same for displayed text (constants and formulas).  It is practical only for testing things that need a lot of comments generated.  Such test data can be created with MarkCells and then run the following macro.

Sub TextIntoComments()
    Dim cell As Range
    Selection.ClearComments
    For Each cell In Selection
      If Trim(cell.Text) <> "" Then
        cell.AddComment cell.Text
        cell.Comment.Visible = False
        cell.Comment.Shape.TextFrame.AutoSize = True
      End If
    Next cell
End Sub 

Resizing All Cell Comments

The following may be a fixup if you have serious problems for size of the comment box.

Sub FitComments()
    Dim c  As Comment
    For Each c In ActiveSheet.Comments
        c.Shape.TextFrame.AutoSize = True
    Next c
End Sub

Related

FORMULA, display, in use in another cell

Describes how to display the formula used in another cell, and how to display the formatting used.  This information is particularly useful for debugging and for preparing printed documentation of formula and format used directly on the spreadsheet.  Includes how to tell if another cell is a formula or an entry.  Quick method of viewing both formula and format utilizing a msgbox without going to the Format menu. Functions include:  GetFormula, GetFormat, HasFormula, UseFormula, FormulaBox.

Colors used in Cell Comments,

Change the color of cell comments, individually or the tool tip default.

You can change the font size and background colour of an excel comment by changing the windows tooltip setup in the window control panel (Start, settings, control panel, appearance,item: ToolTip); however, Excel insists on using Tahoma as the font. -- Alan Beal.

Which does seem a little backwards since the actual tooltip for looking at buttons etc looks terrible in Shotgun, for instance, but might be tolerable as a default cell comment.  Incidentally you can change the cell comment text individually to Shotgun or any other font including parts of the cell comment, just not as a default.  You have less control in a Text Box.

Q173798 -- XL97: How to Change the Font in a Cell Comment,
macros to change an existing Comment or all comments in a workbook.

Sometimes the cell border is responsible for the entire comment box turns black or real wide borders.  change the border width, by formatting the individual comment, and for the default -- Go to Windows Control Panel > Display Property > appearance tab > at scheme dropdown box set Default Windows. (Orlando Filho, 2001-10-08 worksheet functions).  Another cause mentioned was having color resolution at 16-bit intead of at least 32-bit for Graphics card (Sonny 2002-02-02), and also mentioned reducing the HW accerlation would also make it work properly -- these setting are under Settings, CControl Panel then Display, Settings (tab), Colors.  The speed is also from the Control Panel then Systems icon, the Performance (tab), Graphics [button] then scale.

Change Indicator and Highlight changes (pink sheet with pencil)

A little black triangle in the upper left corner of a cell indicates changes in a cell for a project library; otherwise, it looks similar to a cell comment indicator.  See warnings about future inaccessibility to edit, view, or change id of macros in a project library.

MarkCells() is used to create test data by populating cells within the selected range with their own addresses.  MarkSepAreas() includes cell address

and area number for creating test data across multiple ranges. i.e. A1-1, B1-1, B2-2,C2-2,D2-2

Resizing the Name box, Robert Gelb, 16 Dec 2000, misc.

Q213638 -- XL: How to Create a Macro That Counts Comments in Excel, (why not just use ActiveSheet.Comments.Count ).

Please send your comments concerning this web page to: David McRitchie mailto:DMcRitchie@msn.com