Description:

Comments are helpful when you want to show some remarks or comments on particular cell, you can Add Clear Comments in Excel VBA.

Add Clear Comments in Excel VBA – Solution:

You can use AddComment and ClearComments methods to do this.

Example:

Add Clear Comments in Excel VBA
The following code will show you how to add and clear comments using Excel VBA.

Code:
Sub sbAddComment()
'Deletes Existing Comments
Range("A3").ClearComments
'Creates Comment
Range("A3").AddComment
Range("A3").Comment.Text Text:="This is Example Comment Text"
End Sub
Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a Module for Insert Menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to execute it
Practical Applications:

If you have lots of Cells to add comments, we general write the comments in another set of range and add using VBA.

Following is the Example program to add the comments from a range.

Sub sbAddComment_Example()
For iCntr = 1 To 30
'Clear if any existing comments
Range("A3").ClearComments
'Add a Comment from Column B
Range("A" & iCntr).AddComment
Range("A" & iCntr).Comment.Text Text:=Range("B" & iCntr).Value
Next iCntr
End Sub
Explantion:
  1. For Loop is to iterate from 1 to 30 rows, you can change as per your require mt
  2. ClearComents method is using to clear the existing comments if any
  3. AddComments method will add the comment in the particular range
  4. Comment.Text property is for adding the Commet text or message which you want ot show it the user on mose hover on a range
Instructions:
  1. Open an excel workbook
  2. Enter some data in Column A and B as per your requirement, to execute the above program you need to enter some data from Range A1 to Range B30.
  3. Press Alt+F11 to open VBA Editor
  4. Insert a Module for Insert Menu
  5. Copy the above code and Paste in the code window
  6. Save the file as macro enabled workbook
  7. Press F5 to execute it

Hey! Join Our Community

Get Quick Responses & Experts' Answers in Minutes!
Get Notified - When Answered Your Question!