VBA code to insert, delete and control comments

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:

• msoShapeRoundedRectangle
• msoShapeOval
• msoShapeRectangle

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.

---

What next?

Discover how you can automate your work with our Excel courses and tools.