Cell Comments

cellcomment1Cell Comments

Location:   http://www.mvps.org/dmcritchie/excel/ccomment.htm

Home page: http://www.mvps.org/dmcritchie/excel/excel.htm

Auxiliary page: http://www.mvps.org/dmcritchie/excel/ccommentx.htm (note “x”)

Code page: http://www.mvps.org/dmcritchie/excel/code/ccomment.txt

[SelectComments], [MyComment], [WriteComments], [hascomment], [addcomments], [printcommentsbycolumn], [IsCommentsPresent], [change], [tracking], [font], [ccformulas] FormulasIntoComments, [cctext], [protection], [resizing], [colors], [validation conversions], [related], [problems],

This page contains some VBA macros.  If you need assistance to install or to use a macro please refer to my  «Getting Started with Macros« or delve into it deeper on my Install page.

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.

Shortcut keys: Shift+F2 — Edit Cell Note (Edit Cell Comment) — Right Click on cell, Edit comment
Through File Menu:  Insert (menu), Insert Comment (or Edit Comment)
(Excel 2007:  Review, Comments, New Comment)

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

Comment indicators do not show up well with cells that have a red or magenta interior color.
Excel 2000 provides background shading for selected cells, so to help show up such cells — use
Ctrl+A, Ctrl+G, Special, Comments — or use a macro

     Sub SelectComments()
     End Sub

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 double-click 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 includes 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

Grouping Sheets will not allow use of Special Cells such as selecting and copying comments.  Same applies within a macro — you cannot paste special to multiple sheets at once.

Print Comments using Excel Options
You can print the comments when you print the workbook,
File, Page Setup, Sheets,   (Excel 2007: Page Layout, Page Setup, Sheets,)
[x] Row and Column Headings
Comments: At End of Sheet

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

Option Explicit
Sub writeComments()
' Nick Hodge -- 1999-11-13 in MISC, prints cell  comments out to a
'  text file, with their address.
' 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

The above will invoke Internet Explorer rather than notepad.  An example using ShellExecute instead of Shell can be found in

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   (#mycomment)

Function to obtain cell comments from another cell, was posted by Dana DeLouis (1998/05/17, excel.misc)

Function MyComment(rng As Range)
    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.

If you use the above macro as is would suggest you install in the workbook itself so you can decide whether to later remove activesheet.Volatile


You can avoid a lot of calculations, if you remove the volatile statement and do your own Ctrl+Alt+F9 to Recalculate all cells on all worksheets in all open workbooks.

To make the recalculation more automatic you can use an Event macro to recalculate when a worksheet is activated, and an double click Event macro to to force the sheet activatation macro so you can see change immediately without actually switching sheets and back simply to reactivate teh worksheet. (see event.htm#recalculate).

Function to detect cell comments in another cell   (#hascomment)

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
End Function
=IF(HasComment(B3),B3,"No Comment")

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 onperformance.  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 (#addcomments)

Macro posted by Dave Ramage to obtain cell comment values for a single range from the text values of another matching single range (based on cell count of 1st range).  Modified to use TEXT value which is 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", _
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 _
        'already comment, so delete then add
        rngCells.Areas(1).Cells(lCnt).AddComment _
    End If
Next lCnt
End Sub

Macro to populate another sheet with comments by column (#printcommentsbycolumn)

Cell comments by default are printed (pagesetup/sheets) by row then column.

This macro will populate a new worksheet with the cell address, and the cell comment. You can view or print that worksheet. As you requested cell comments will be displayed down one column and then down the next column(s) for cells that have comments.  This macro has an example, and can also be found in the coding for this page.

Sub PrintCommentsByColumn()
  'David McRitchie, misc,  2002-08-09, corrected 2002-08-10
  'reference:  http://www.mvps.org/dmcritchie/excel/ccomments.htm
  Dim cell As Range
  Dim myrange As Range, myrangeC As Range
  Dim col As Long
  Dim RowOS As Long
  Dim wsSource As Worksheet
  Dim wsNew As Worksheet
  If ActiveSheet.Comments.Count = 0 Then
    MsgBox "No comments in entire sheet"
    Exit Sub
  End If
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual 'xl95 uses xlManual
  Set wsSource = ActiveSheet
  Set wsNew = ActiveSheet
  With wsNew.Columns("A:C")
      .VerticalAlignment = xlTop
      .WrapText = True
  End With
  wsNew.Columns("B").ColumnWidth = 15
  wsNew.Columns("C").ColumnWidth = 60
  wsNew.PageSetup.PrintGridlines = True
  RowOS = 2
  wsNew.Cells(1, 3) = "'" & Application.ActiveWorkbook.FullName & " -- " & _
  For col = 1 To ActiveSheet.UsedRange.Columns.Count
     Set myrangeC = Intersect(ActiveSheet.UsedRange, Columns(col), _
     If myrangeC Is Nothing Then GoTo nxtCol
     For Each cell In myrangeC
        If Trim(cell.Comment.Text) <> "" Then
           RowOS = RowOS + 1
           wsNew.Cells(RowOS, 1) = "'" & cell.Address(0, 0) & ":"
           wsNew.Cells(RowOS, 2) = "'" & cell.Text
           wsNew.Cells(RowOS, 3) = "'" & cell.Comment.Text
        End If
     Next cell
  Next col
  Application.Calculation = xlCalculationAutomatic 'xl95 uses xlAutomatic
  Application.ScreenUpdating = True
End Sub

Function to check for presence of comments (#IsCommentsPresent)

Andrew Kirienko 1997-09-07

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

=IF(iscommentspresent, "WS Has Cell Comments", "No Cell Comments on WS")

Adding / Changing Cell Comments   (#change)

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 -- right-click on the sheettab of the sheet to
' be used in and choose 'view code'.  Paste this Worksheet
' event macro into the module.
   Cancel = True   'Get out of edit mode
   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

Paste Special Comments   (#pastecomments)

Will paste comments from a copied block of cells to another block of cells.

Sub pastespecialcomments()
    Selection.PasteSpecial Paste:=xlPasteComments, _
        Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

Tracking single cell Changes to value   (#tracking)

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  'D.McR 2001-11-02 worksheet.functions
   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
      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   (#font)

Excel uses the Tahoma font for new comment boxes, and you cannot change that default.  You can set the default interior color, the font color, and the fontsize in the Control Panel (Display, Appearance, Tool Tip).  Also see Retain a copy of your Original Control Display Settings for some precautions to backup what you already have for a color display scheme. before changing them.

Formatting for existing cell comments including the font type can be changed manually by right-clicking on the cell comment outline and choosing Format.  Changes can also be made with a macro to simplify changes.

The following macro is attributed to John Walkenbach to change existing cell comments for the active cell (selected).  Debra Dalgleish has a similar but different macro, read her comments.

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
End Sub

Change the above to suit yourself, for example:  Terminal 14 provides a change that you can’t miss for demonstration; whereas, reader Brian Cavanaugh, 2008-01-04 suggests showing a more practical solution with Arial 10

Sub Format_Comments_Arial_10()
    Dim Cell As Range
    For Each Cell In Cells.SpecialCells(xlCellTypeComments)
        With Cell.Comment.Shape.TextFrame
            .Characters.Font.Name = "Arial"
            .Characters.Font.Size = 10
            .AutoSize = True
        End With
        Cell.Comment.Shape.TextFrame.AutoSize = True
End Sub

Placing Formulas into Cell Comments   (#ccformulas)

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
    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 GetFormulaon my formula page.  Others prefer to print the formulas, such as John Walkenbach’s Creating a List of Formulas (Tip 37) .

Placing Displayed Text into Cell Comments   (#cctext)

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 MarkCellsand then run the following macro.  Note use of Del key does not affect cell comments.

Sub TextIntoComments()
    Dim cell As Range
    For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
      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

To populate the selected cells with the text on the right.
This is more practical when working with extensive notes as the column to the right with the original notes could later be eliminated.
Note use of Del key does not affect cell comments.  Deleting a column is more effective in removing everything.

Sub TextIntoComments_GetFromRight()
'/ place comment into cell to left of each selected cell
'/ for eventual removal of the selected column with notes
    Dim cell As Range
    For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
      If Trim(cell.Offset(0, 1).Text) <> "" Then
        cell.AddComment cell.Offset(0, 1).Text
        cell.Comment.Visible = False
        cell.Comment.Shape.TextFrame.AutoSize = True
      End If
    Next cell
End Sub

Protection   (#protection)

When protecting a worksheet (Tools, protection), protection of changes to comments is controlled on a dialog that then appears by “objects” (other choices: contents, scenarios).

Remove the User name from Cell Comments (#remove)

This code will remove the User Name found in tools, General, username from the beginning of comment cells.

Sub CommentRemoveUserName()
   Dim cmt As Comment  'DMcRitchie, misc, 2004-04-01
   Dim LUSR  As Long
   Dim USR As String
   USR = LCase(Application.UserName) & ":" & CHR(10)
   LUSR = Len(USR)
   For Each cmt In ActiveSheet.Comments
     If Left(LCase(cmt.Text), LUSR) = USR Then
         cmt.Text Mid(cmt.Text, LUSR + 1)
     End If
End Sub

Resizing All Cell Comments, Resize   (#resizing)

The following may be a fixup if you have serious problems for size of the comment box.  (Format, Alignment, Automatic size). .

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

To set all comments to a specific size in points, this example for 2 inches wide and 1 inch high.

Sub ResizeALLcomments()
    Dim c  As Object
    For Each c In ActiveSheet.Comments
        c.Shape.Width = 144
        c.Shape.Height = 72
    Next c
End Sub

For a better solution see microsoft.public.excel.misc thread in Google on 1997/12/14
Move and size with cells, cell comments can be Formatted as such.
– Move and size with cell, |  Move but don’t size with cell, |  Don’t move or size with cell (default)

Manually Hide/Display Cell Comment Indicators (#hide)

Display, Hide Display of cell comments:
tools, options, view, comments: none

Print, Hide Cell comment indictors:
Page Setup, sheet, print comments: none

Toggle Display of Cell Comment Indicators (#toggle)

Macro to toggle between nocomment indicator, comment indicator, and indicator & comment.

You may attach the macro to a toolbar or to a shortcut key.

Sub toggle_comment_indicator()
    If Application.DisplayCommentIndicator = xlNoIndicator Then
       Application.DisplayCommentIndicator = xlCommentIndicatorOnly
    ElseIf Application.DisplayCommentIndicator = xlCommentIndicatorOnly Then
       Application.DisplayCommentIndicator = xlCommentAndIndicator
       Application.DisplayCommentIndicator = xlNoIndicator
    End If
End Sub

Colors used in Cell Comments (#colors)

Cell Comment indicator triangle, change color of, Stratos Malasiotis, 2000-07-08
Also check out another macro doing same thing, mainly by Dave Peterson, 2003-04-25.

Color Triangles in Excel, Red, Black, Green, Purple.   (covered on colors page — colors.htm#triangles)

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

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.

Problems with Cell Format Colors (#problems /#cboxblack)

Problems with the entire comment box turning black or real wide borders.  change the border width, by formatting the individual comment.

the following are my settings:
fill color: automatic (looks white)
style is set to 3/4 pt
weight is set to .75 pt >—– probably have this set high

Changing the default in Control Panel:  Go to Windows Control Panel > Display Property > appearance tab > at scheme dropdown box set Default Windows. (Orlando Filho, 2001-10-08worksheet functions).  Another cause mentioned was having color resolution at 16-bit instead of at least 32-bit for Graphics card (Sonny 2002-02-02), and also mentioned reducing the HW acceleration would also make it work properly — these setting are under Settings, Control Panel then Display, Settings (tab), Colors.  The speed is also from the Control Panel then Systems icon, the Performance (tab), Graphics [button] then scale.

Macros (#code)

Code Macro or Function code Description
Sub AddComments Add Comments from another range (Dave Ramage, 2001-04-11)
Sub CommentPopulateSelection All comments in Select get same test (D.McR 2003-10-05) creates uniform test data, with cell address as comment.  Used to test cell migration.
Sub CommentPopulateText Create Comments from Cell Text (D.McR, 2002-08-10)creates comments from the displayed text (.text) rather than simply the cell value (.value).
Sub CommentTestGeneration Create Comment Test Data (D.McR, 2002-08-10)
Sub CommentRemoveUserName Remove User Name from Comment (D.McR, 2004-04-01)
Sub CommentsFromValidationBox Create Comments from Validation Messages (D.McR, 2004-06-05)– such conversions are not recommended.
Sub CommentsSelectAll Select All Comments on Sheet (D.McR, 2004-06-05)
Sub CommentsToValidationBox Convert Comments to Validation Box Messages (D.McR, 2004-06-05), for those that don’t want to see comment tags — such conversions are not recommended.
Sub FillComments Text Into Comments from Selection (D.McR, )
Sub FitComments Fit Comments on Sheet with Autosize (D.McR, 2001-11-15)
Sub FitComments2 Fit Comments on Sheet with Autosize, creating comments from text (D.McR, test)
Sub FormulasIntoComments Formulas into Comments from Selection, (D.McR, …)
Sub ListComms List comments (Dick Kusleika, 2002-11-13)
Sub MakeComment Make Comment (D.McR, test)
Sub PasteSpecialComments Paste Special Comments — after a Copy (Ctrl+C) (D.McR)
Sub PrintCommentsByColumn Print Comments on new sheet (D.McR, 2002-08-09)
Sub SelectAllValidations Select All Cell Validations on Sheet (D.McR, 2004-06-05)
Sub TextIntoComments Text Into Comments from Selection, refits size (D.McR)
Sub ToggleComments Toggles between No Comment, Indicator Only, both Indication & Comment (D.McR 2005-03-31)
Sub WriteComments Write Comments to Separate file (mod. From Nick Hodge, 1999-11-13), modified to view in notepad, but suggest using PrintCommentsByColumn, or J-Walk Tip37 by row.
Function HasComment Truth test for Cell Comment (Patrick Molloy, 2001-11-17)
Function MyComment Show comment used in another cell(see code above) (Dana DeLouis, 1998-05-17).

Create a Subroutine and Function Table for Open Workbooks, shows workbook, module, #lines, duplicate indications

Some alternatives to Cell Comments   (#alternatives)

Some alternatives to cell comments:

  • To be seen on formula bar for cell A100: (additional usage of Offset)
    =Sum(A$2:Offset(A100,-1,0))+N(“Allows insertion of rows immediately above this formula”)
  • Validation (see Related below.

Related   (#related)

Cell Comments, some other pages on the subject

See contextures.com, Debra Dalgleish’s site (Tech Tips Index).
Basics: Part 1 of 3 web pages of Excel Comments, Use & Tips.
Change Comment ShapeAdd a Picture to a Comment ‡
Insert a Plain Comment (CommentAddOrEdit)
Insert a Formatted Comment (CommentAddOrEditTNR)
Reset Comments to Original Position« (ResetComments)
Show Comments on Active Sheet for viewing (ShowSheetComments), show/hide comments of the active sheet
Copy Comments to Another Worksheet (ShowComments)
Copy Comments from All Sheets to Another Worksheet (ShowCommentsAllSheets)
Copy Comments to Microsoft Word (CopyCommentsToWord) (
Worksheet with Comment Indicators – for printing (CoverCommentIndicator, RemoveIndicatorShapes)

John Walkenbach, formatting comments
Change the formatting of cell comments (Tip 12) from archives, and probably the same as tip075, and
Excel User Tip: Changing the Default Cell Comment Formatting(Tip 75),   J-W site search for:  cell +comment

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.

Creating a List of Formulas (Tip 37) « can be found on John Walkenbach’s site (Tip 37).

It uses less coding but is identical in function, to the GetFormulaInfo example from Microsoft.  also see my Formula page.

Creating a Worksheet (Tip 28) Map: Creates a map for a worksheet.

The map color-codes each cell by type (text, number, or formula) — John Walkenbach.   also see my Formula page.

Displaying Comments on Protected Sheets. Norman Harker

Fonts, Getting a List of Installed Fonts (Tip 79) — John Walkenbach  also see my Formula page.

The Font Thing — Sue Fisher [update notes], thanks to Jim Rech (2000-03-15) for telling us.

Provides information on installed and uninstalled fonts, font samples for your text, including use of two fonts in samples.  also see my Formula page.

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.  also see my Formula page.

List all comments from all sheets, Dick Kusleika, 2002-09-12, use

csh.Cells(Rows.Count, “A”).End(xlUp).Offset(1, 0) instead of csh.Range(“a65536”).End(xlUp).Offset(1, 0)
useon error resume next: For each cell in sh.cells.SpecialCells(xlCellTypeComments)
instead ofFor Each cell In sh.UsedRange — in XL97 use xlComments–

Formula List will list the formulas used in a specified range.

Part of the Simtools.xla and Formlst.xla add-ins available from Northwestern Univ for heavy statistical modeling, Monto Carlo simulation and Tornado simulation — I’m impressed.  In any case it reinforces the fact that you need good documentation to get much accomplished — and that part I do understand.  Also see Creating a List of Formulas (above), and myFormula page.

Insert Graphic into Cell Comment, TextBox, or AutoShape. (Mike from Perth)

Right-click on border of Cell comment, Format Comment, Colors and Lines (tab), Fill: Color (drop-down), Fill Effects, Select Picture
Also see Debra Dalgleish’s Comment Tips page, topic: Add a Picture to a Comment (contextures)
Also see Display picture based on cell value, McGimpsey

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

Move and size with cells, cell comments can be Formatted as such.

Q170081 – XL97: “Cannot Shift Objects Off Sheet” Error Hiding Columns

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

213766 – XL2000: Sample Macro to Remove User Name from Comment

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

Shapes like pictures, comment boxes, and text boxes can be hidden/unhidden

with a Shortcut.  Ctl+6 Alternates between hiding objects, displaying objects, and displaying placeholders for objects.

Validation, normally used to discourage user from entering bad data, has a Title and Message body that

will be shown similar to a comment but upon cell selection (black color only). [code/ccomments.txt has some conversions].  As an alternative to a comment box which appears when comment indications are on and one hovers over the cell (in Excel 2000) you can use Data Validation” to Add Message for User, whereby An Input Message can be displayed when the cell with data validation is selected.