Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

How to Read, Write, Delete and Move excel comments

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"
     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.

Contact Form


Email *

Message *

Popular Posts