How to Display Comments with Excel VBA

When working in Excel, it is useful to use comments, as they simplify viewing text attached to cells. To create and manage comments in MS Excel 2010, there is a Comments group on the Review tab of the ribbon. Alternatively, you can use the DisplayCommentIndicator property of the Application object to work with comments programmatically.

The DisplayCommentIndicator property of the Application object allows you to control the display style of comments. Acceptable values for this property are the following XlCommentDisplayMode constants:

• xlNoIndicator — no indicator;
• xlCommentIndicatorOnly — indicator only;
• xlCommentAndIndicator — both comment and indicator.

In the demonstration example, when the workbook is opened, comments are added to cells A1 and A4. When cell A1 is selected, both the comment and its indicator are displayed; when any other cell is selected, the comments are hidden.

Managing the Display of Comments and Their Indicators. ThisWorkbook Module

Private Sub Workbook_Open()
    Worksheets(1).Range("A1").ClearComments
    Worksheets(1).Range("A1").AddComment
    Worksheets(1).Range("A1").Comment.Visible = True
    Worksheets(1).Range("A1").Comment.Text Text:="This is cell A1"
    Worksheets(1).Range("A4").ClearComments
    Worksheets(1).Range("A4").AddComment
    Worksheets(1).Range("A4").Comment.Visible = True
    Worksheets(1).Range("A4").Comment.Text Text:="This is cell A4"
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
    If Sh.Name = Worksheets(1).Name Then
        If Target.Address = "$A$1" Then
            Application.DisplayCommentIndicator = xlCommentAndIndicator
        Else
            Application.DisplayCommentIndicator = xlNoIndicator
        End If
    End If
End Sub