Some Excel users love comments; they put comments all over their workbooks. Other users despise them as they are unsightly and annoying. Whatever your preference, you can control them with VBA; add, delete, list on a worksheet – it can all be done with VBA.
Adapting the code to your needs
It is unlikely that any of the code below will meet your exact requirements. Every code snippet uses variables to hold either the worksheet, workbook comment, cell or a setting value. By changing those variables, the code can easily be changed and combined with other code snippets to meet your specific requirements.
Basic comment actions
This first group of macros feature some of the basic actions you may include as part of a longer procedure.
Test if a cell has a comment
The following code will check if the active cell has a comment. If so, it will output a message box to state that a comment exists.
Sub TestCellForComment() 'Create variables Dim c As Range Dim commentText As String 'Set variables Set c = ActiveCell 'Try to set variable. If error then comment does not exist On Error Resume Next commentText = c.comment.Text On Error GoTo 0 'If comment exists then display message box If commentText <> "" Then MsgBox "Cell contains a comment" End If End Sub
Add a comment to a cell
The following code will insert the text “Insert my comment” as a comment attached to the active cell.
Sub AddComment() 'Create variables Dim c As Range Dim commentText As String 'Set variables commentText = "Insert my comment" Set c = ActiveCell 'Add comment c.AddComment 'Change the comment c.comment.Text Text:=commentText End Sub
Get the text from a comment
The following code will capture text from a comment and display it within a message box.
Sub DisplayCommentFromCell() 'Create variables Dim c As Range Dim commentText As String 'Set variables Set c = ActiveCell 'Try to set variable. If error then comment does not exist On Error Resume Next commentText = c.comment.Text On Error GoTo 0 'If comment exists then display comment If commentText <> "" Then MsgBox commentText End If End Sub
Clear comments from a worksheet
The following macro will clear the existing comments from the worksheet.
Sub ClearCommentsWorksheet() 'Creates variables Dim ws As Worksheet 'Set variables Set ws = ActiveSheet 'Clear comments from worksheet ws.Cells.ClearComments End Sub
Clear comments from a range
The following macro will clear the comments from a specified set of cells (A1-B20 of the active sheet) in this example.
Sub DeleteCommentsFromRange() 'Creates variables Dim ws As Worksheet Dim rng As Range 'Set variables Set ws = ActiveSheet Set rng = ws.Range("A1:B20") 'Clear comments from worksheet rng.ClearComments End Sub
Looping through comments
Some actions can be applied to all comments at the same time (such as clearing them). But, other actions must be applied individually by looping through each of the comments one-by-one.
Loop through all comments in the worksheet
The following VBA code will loop through all the comments in the active worksheet.
Sub LoopThroughCommentsInWorksheets() 'Create variables Dim ws As Worksheet Dim com As comment 'Set variables Set ws = ActiveSheet 'Loop through each comment on worksheet For Each com In ws.Comments 'Do somthing to each comment 'Use com. to reference each comment Next com End Sub
Loop through all comments in the workbook
The following expands on the code above and loops through all the comments in the active workbook.
Sub LoopThroughCommentsInWorkbook() 'Create variables Dim wb As Workbook Dim ws As Worksheet Dim com As comment 'Set variables Set wb = ActiveWorkbook 'Loop through each worksheet For Each ws In wb.Worksheets 'Loop through each comment on worksheet For Each com In ws.Comments 'Do something to each comment 'Use com. to reference each comment Next com Next ws End Sub
Cells with comments
When thinking about comments, we also need to consider the cell to which the comment is attached. The following examples specifically relate to those types of cells.
Select all the cells with comments
Excel provides a method to select all the cells with comments, which is used in the macro below.
Sub SelectCellsWithComments() 'Create variables Dim ws As Worksheet 'Set variables Set ws = ActiveSheet 'Select the cells with comments ws.Cells.SpecialCells(xlCellTypeComments).Select End Sub
Loop through all cells with comments in the worksheet
Where methods must be applied to each cell individually, it is necessary to loop through them one-by-one. The code below will loop through each cell in the active worksheet which contains a comment.
Sub LoopThroughAllCellsWithCommentsWorksheet() 'Create variables Dim ws As Worksheet Dim c As Range 'Set variables Set ws = ActiveSheet 'Loop through each comment on worksheet For Each c In ws.Cells.SpecialCells(xlCellTypeComments) 'Do somthing to each cell 'Use c to reference each cell Next c End Sub
Loop through all cells with comments in the workbook
The following expands on the code above and loops through all cells in the workbook which have comments attached.
Sub LoopThroughAllCellsWithCommentsWorkbook() 'Create variables Dim wb As Workbook Dim ws As Worksheet Dim c As Range 'Set Variables Set wb = ActiveWorkbook 'Loop through each worksheet For Each ws In wb.Worksheets 'Loop through each comment on worksheet For Each c In ws.Cells.SpecialCells(xlCellTypeComments) 'Do something to each cell 'Use c to reference each cell Next c Next ws End Sub
Change comment display settings
Comment display settings can be changed to one of three options:
- The comment is hidden
- The comment is hidden but the indicator (i.e., the red triangle) is visible
- The comment and indicator are visible
The following macro will hide the comment and indicator.
Sub ChangeCommentDisplay() 'Create variables Dim indicatorType As Long 'Set variables indicatorType = xlNoIndicator 'Apply comment indicator Application.DisplayCommentIndicator = indicatorType End Sub
To apply the other settings change this line of code:
indicatorType = xlNoIndicator
For one of these lines of code:
Show comment indicator:
indicatorType = xlCommentIndicatorOnly
Show comment indicator and comment:
indicatorType = xlCommentAndIndicator
Displaying and printing comments on a separate page
If you would prefer comments to be displayed in a single worksheet or a single printed page, then these next two macros will be useful.
Print comments on an additional page
Within Excel, there are settings which control comments. We don’t tend to look for them unless we have a specific problem or requirement. One of these options controls the print settings. The macro below will print all the comments on a separate page.
Sub PrintSetupListComments() 'Create variables Dim ws As Worksheet Dim printOptions As Long 'Set variables Set ws = ActiveSheet printOptions = xlPrintSheetEnd 'Set comments to print on an additional page ws.PageSetup.PrintComments = printOptions End Sub
The PrintComments setting has three options. Therefore this line of code:
printOptions = xlPrintSheetEnd
Can be exchanged for one of these options.
Set comments to print if they are displayed:
printOptions = xlPrintInPlace
Set comments to never print:
printOptions = xlPrintNoComments
List all comments on a separate worksheet
The following code will create a new worksheet which lists all the comments. There is limited error checking, so if you already have a worksheet called “Comments”, it will display an error.
Sub ListAllCommentsOnSeparateSheet() 'Create variables Dim wb As Workbook Dim ws As Worksheet Dim newWs As Worksheet Dim c As Range Dim i As Integer 'Set variables Set wb = ActiveWorkbook Set newWs = wb.Worksheets.Add newWs.Name = "Comments" 'Insert header row into Comments Worksheet newWs.Cells(1, 1).value = "Sheet" newWs.Cells(1, 2).value = "Cell Ref" newWs.Cells(1, 3).value = "Comment" 'Loop through each worksheet For Each ws In wb.Worksheets 'Ignore errors from worksheets with no comments On Error Resume Next 'Loop through each cell with a comment For Each c In ws.Cells.SpecialCells(xlCellTypeComments) 'If there are no errors (i.e. comments exist) If Err.Number = 0 Then 'Increment counter i = i + 1 'List comments in the Comments Worksheet newWs.Cells(i + 1, 1).value = ws.Name newWs.Cells(i + 1, 2).value = c.Address newWs.Cells(i + 1, 3).value = c.comment.Text End If Next c 'Reset error checking On Error GoTo 0 Next ws End Sub
Change the shape of all comments boxes on a worksheet
Comment boxes always seem to be boring squares, but they can be a variety of shapes. Use the following code to change all the shapes to boxes with a folded corner.
Sub ChangeCommentsBoxShape() 'Create variables Dim ws As Worksheet Dim com As comment Dim shapeType As Long 'Set variables Set ws = ActiveSheet shapeType = msoShapeFoldedCorner 'Loop through each cell with a comment For Each com In ws.Comments 'Change the shape com.Shape.AutoShapeType = shapeType Next com End Sub
Some alternative shapes you could try are:
If you want other shapes, probably best to use the macro recorder to find the name of the shape, or you could use the IntelliSense feature. IntelliSense will list all the available options as shown in the screenshot below.
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: