VBA and comments

Comments in Excel are a convenient way to leave notes about the results of the calculations you made or otherwise explain your work within the worksheet. VBA allows you to easily add and delete comments. The following text will describe how to create programs by which you add or delete comments from within an active cell.

Comment is a variable of a string type. In this example we’ll directly assign a value to a comment. In practice, you will probably retrieve it from a control within a user form.

Dim cmt As String
cmt = “My Comment”

The string cmt can be a combination of multiple strings, and it is also possible to change the style within the text which represents it. Further we should check whether the selected cell has a comment. If not, using the AddComment method, we’ll add it. Otherwise, program should show an error message.

ActiveSheet.Range(Application.ActiveCell.Address).AddComment cmt

That is, the final program for adding comments looks like this:

Sub Add_comment()

Dim cmt As String
cmt = “My comment”

If ActiveCell.Comment Is Nothing Then
  ActiveSheet.Range(Application.ActiveCell.Address).AddComment cmt
Else
  MsgBox “This cell already has a comment”
End If

End Sub

Deleting comments is even simpler. It only needs to be positioned on the active cell, and then delete the comment by using the appropriate method. The final program for deleting comments looks like this:

Sub Delete_comment()

ActiveSheet.Range(Application.ActiveCell.Address).Comment.Delete

End Sub