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 macroSub SelectComments() Selection.SpecialCells(xlCellTypeComments).Select 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. ' 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 SubThe 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, was posted by Dana DeLouis (1998/05/17, excel.misc)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=MyComment(B3)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 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(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 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 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", _ 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
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 Sheets.Add Set wsNew = ActiveSheet wsSource.Activate 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 & " -- " & _ Application.ActiveSheet.Name For col = 1 To ActiveSheet.UsedRange.Columns.Count Set myrangeC = Intersect(ActiveSheet.UsedRange, Columns(col), _ Cells.SpecialCells(xlCellTypeComments)) 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 nxtCol: Next col wsNew.Activate Application.Calculation = xlCalculationAutomatic 'xl95 uses xlAutomatic Application.ScreenUpdating = True End Sub
Andrew Kirienko 1997-09-07Public Function IsCommentsPresent() As Boolean IsCommentsPresent = ( ActiveSheet.Comments.Count <> 0 ) End Function
=IF(iscommentspresent, "WS Has Cell Comments", "No Cell Comments on WS")
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
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
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 Else Target.Comment.Text (Target.Comment.Text & Chr(10) & ccc) End If Target.Comment.Shape.TextFrame.AutoSize = True End Sub
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 SubChange fontsize of all cell commentsSub 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 SubChange 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 10Sub 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 Next End Sub
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 SubMy 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. Others prefer to print the formulas, such as John Walkenbach's Creating a List of Formulas (Tip 37) .
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. Note use of Del key does not affect cell comments.Sub TextIntoComments() Dim cell As Range Selection.ClearComments 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 Selection.ClearComments 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
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).
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 Next End Sub
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 SubTo 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 SubFor 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)
Display, Hide Display of cell comments:
tools, options, view, comments: none
Print, Hide Cell comment indictors:
Page Setup, sheet, print comments: none
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 Else Application.DisplayCommentIndicator = xlNoIndicator End If End Sub
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-08 worksheet 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.
|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).|
Some alternatives to cell comments:
- 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 Shape, Add 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 my Formula 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.
This page was introduced on December 19, 1999.
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved