Insert Comments with Excel VBA

Introduction to VBA Comments in Excel

In Excel, Comments (also known as Cell Notes) are used to add extra information or explanations to cells. These comments can be inserted manually by right-clicking a cell and choosing « Insert Comment, » or you can automate this process using VBA (Visual Basic for Applications).

In this tutorial, I’ll show you how to insert comments in cells using VBA code, and provide a detailed explanation of each part of the code.

The VBA Code: Inserting Comments in Excel

Here’s a detailed VBA code example to insert comments into a cell and also customize the comment:

Sub InsertCommentExample()
    ' Declare variables to reference the target cell and the comment text
    Dim targetCell As Range
    Dim commentText As String   
    ' Set the target cell where the comment will be inserted
    Set targetCell = Range("B2")   
    ' The text of the comment to be inserted
    commentText = "This is a comment added via VBA!"
    ' Check if the cell already has a comment
    If Not targetCell.Comment Is Nothing Then
        ' If the cell already has a comment, delete the existing one
        targetCell.Comment.Delete
    End If   
    ' Insert a new comment in the target cell
    targetCell.AddComment commentText   
    ' Optional: Format the comment (e.g., set the background color)
    With targetCell.Comment.Shape
        ' Change the background color of the comment box
        .Fill.BackColor.RGB = RGB(255, 255, 153)  ' Light Yellow color       
        ' Change the font color inside the comment
        .TextFrame.Characters.Font.Color = RGB(0, 0, 255)  ' Blue font color       
        ' Set the font size of the comment
        .TextFrame.Characters.Font.Size = 10       
        ' Optional: Set the comment box to be visible all the time
        .Visible = True
    End With
End Sub

Step-by-Step Explanation of the Code

1. Declaring Variables

Dim targetCell As Range

Dim commentText As String

In this section, we declare two variables:

• targetCell: This variable will hold a reference to the cell where we want to insert the comment.
• commentText: This variable will hold the text that will be displayed inside the comment.

2. Setting the Target Cell

Set targetCell = Range(« B2 »)

This line sets the targetCell to cell B2, meaning the comment will be added to cell B2. You can change « B2 » to any other cell reference, such as « A1 », « C5 », etc.

3. Defining the Comment Text

commentText = « This is a comment added via VBA! »

Here, the text for the comment is stored in the variable commentText. You can change the text to whatever you need.

4. Checking for Existing Comments

If Not targetCell.Comment Is Nothing Then

    targetCell.Comment.Delete

End If

Before inserting a new comment, it’s important to check if the target cell already has a comment. The code checks if the Comment property of the targetCell is Nothing. If it’s not Nothing, that means there is an existing comment. The code then deletes the existing comment to avoid leaving multiple comments in the same cell.

5. Inserting the Comment

targetCell.AddComment commentText

This line of code inserts a new comment in the targetCell and sets its text to whatever is in the commentText variable. The AddComment method is used to insert the comment into the cell.

6. Formatting the Comment Box (Optional)

With targetCell.Comment.Shape

    .Fill.BackColor.RGB = RGB(255, 255, 153)

    .TextFrame.Characters.Font.Color = RGB(0, 0, 255)

    .TextFrame.Characters.Font.Size = 10

    .Visible = True

End With

This section is optional but allows you to customize the appearance of the comment box.

• .Fill.BackColor.RGB = RGB(255, 255, 153): This sets the background color of the comment box. The RGB(255, 255, 153) represents a light yellow color.
• .TextFrame.Characters.Font.Color = RGB(0, 0, 255): This sets the text color inside the comment box to blue.
• .TextFrame.Characters.Font.Size = 10: This sets the font size inside the comment to 10.
• .Visible = True: This makes the comment visible all the time. By default, comments are only visible when you hover over the cell, but setting .Visible = True forces the comment to always be visible.

Testing the Code

1. Open Excel and press Alt + F11 to open the VBA Editor.
2. In the VBA editor, click Insert > Module to create a new module.
3. Copy and paste the above code into the module.
4. Press F5 or Run to execute the macro. You should see that a comment is added to cell B2 with the specified text and formatting.

Customizing the Code

You can easily customize the code to suit your needs:

• Change the targetCell to any cell you want.
• Modify the commentText to include your desired comment.
• Adjust the formatting (background color, text color, font size, etc.) according to your preferences.

Conclusion

This VBA code shows you how to insert a comment into a specific cell and customize its appearance. You can use this code as a foundation for automating comment insertion across multiple cells or sheets. Excel VBA allows you to automate many tasks, including adding comments, and this example gives you a good starting point for learning how to do it.