In this post let’s look at how to automate Excel cell comments using VBA. If you want to manually enter a cell comment, then you can do it as follows. First right-click inside a cell. Then click on “Insert Comment”.
Then Excel will create a box like this.
Now you can enter your comment.
So if you have a spreadsheet with lots of data, how do you identify the cells which have comments? It is easy. Cells with comments will have red color triangle shape in top right hand corner of the cell. In this example there is a comment in D3 cell.
If you want to read the comment you can take your cursor on top of that cell. Then it will show the comment.
Now let’s look at how to read this comment using VBA. If the comment is in cell D3 then you can read it as follows.
|
Sub ReadComment() Dim StrComment As String StrComment = Range("D3").Comment.Text Debug.Print StrComment End Sub |
If you run above macro, at the beginning comment will be assigned to string variable call “StrComment”. Then it will be printed in immediate window.
Next let’s learn how to write a comment to a specific cell. Below is the code to write a comment to a cell. This will add comment “This is sample comment” to cell G5.
|
Sub WriteComment() Range("G5").AddComment "This is sample comment" End Sub |
Also note that you can replace “Range” keyword with “Cells”. So instead of Range(“D3”) you can write Cells(3,4) and instead of Range(“G5”) you can write Cells(5,7)
So if you want to move comment from B2 cell to C2 cell then you can do it as follows.
|
Sub MoveComment() Dim StrComment As String StrComment = Cells(2, 2).Comment.Text Cells(2, 3).AddComment StrComment End Sub |
Also if you are developing a dynamic application, it is important to check whether there is a comment in the cell before try to read the comment. Because otherwise excel will produce runtime error if program try to read a comment from a cell where there is no comment. You can check whether there is a comment in cell A1 using following code.
|
Sub CheckForComment() If Cells(1, 1).Comment Is Nothing Then MsgBox "No comment" Else MsgBox Cells(1, 1).Comment.Text End If End Sub |
If there is no comment it will give you “No comment” message. If there is a comment then macro will show the comment in a message box.