How to add comments in cell using Excel VBA Easily (2 Types)

Welcome to Excel Avon

Add comments in cell using Excel VBA

Today’s article we are going to write add comments in cell using Excel VBA, commenting out a line in VBA is a very simple yet useful option from a programmer’s perspective. Sometimes, no matter how good we are at programming, we often have difficulty commenting out a single line. Comments can be used to give instructions to the user about the code or function or some statements in layman language. In this article, we will look at ways to add a comment to a cell.

Excel is a very advanced tool that contains thousands of functionalities, but VBA Macro comes into existence when we have to do a repeated task.

How to add comments in cell using Excel VBA

DOWNLOAD USED EXCEL FILE FROM HERE>>

In excel worksheet we have options for add comments in manually, but how we do it in VBA is what we will learn in this article. As we know that it is very easy to do manual formatting in excel, similarly it is easy to do in VBE too, let’s understand.

Through this post, we will know how to be add comments in cell string in worksheet with the help of VBA, then we have to go like last time, first go to the Developer Tab, then click on the option of Visual Basic as shown in the image below.

On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.

Once we insert the module, we will write a subroutine as AddSimpleComments text .

Sub AddSimpleComment()
End Sub

And then Define variable for comment, worksheet, and range.

Sub AddSimpleComment()
Dim cmt As Comment
Dim WS As Worksheet
Dim Rng As Range

End Sub

Now we will set the worksheet 

Sub AddSimpleComment()
Dim cmt As Comment
Dim WS As Worksheet
Dim Rng As Range

Set WS = ActiveSheet
End Sub

Give the range of the cell in which the comments are to be added (“D3”).

Sub AddSimpleComment()
Dim cmt As Comment
Dim WS As Worksheet
Dim Rng As Range

Set WS = ActiveSheet
Set Rng = WS.Range("D3")
End Sub

Now we will use ClearComments to clear the range.

Sub AddSimpleComment()
Dim cmt As Comment
Dim WS As Worksheet
Dim Rng As Range

Set WS = ActiveSheet
Set Rng = WS.Range("D3")

Rng.ClearComments
End Sub

Now we will set cmt then use ‘AddComments‘ function with rng,  In the parameter of Add comment we will write comments text.

Sub AddSimpleComment()
Dim cmt As Comment
Dim WS As Worksheet
Dim Rng As Range

Set WS = ActiveSheet
Set Rng = WS.Range("D3")

Rng.ClearComments
Set cmt = Rng.AddComment("Please put a date in 'dd-mmm-yyyy'")
End Sub

after all, now we will click run button

Comment has been added to the cell, if the comment has been added to the cell once, then running the code again will result in an error.

To make comments visible and hidden, use true or false. If we set the comments visibility to false, then the comments will be hidden and when we enter in the cell then the comments will be visible.

Sub AddSimpleComment()
Dim cmt As Comment
Dim WS As Worksheet
Dim Rng As Range

Set WS = ActiveSheet
Set Rng = WS.Range("D3")

Rng.ClearComments
Set cmt = Rng.AddComment("Please put a date in 'dd-mmm-yyyy'")
cmt.Visible = false
End Sub

Now I will run code, go to the worksheet 

The visibility of the comments has been set to False, so we will not be able to see the comment until we enter the cell.

In this way we can turn on off the visibility.

Add Thread Comments in Cell

Now we will teach how to use add thread comment, how you can add thread comments in cell, and then we will write subroutine  ‘addthreadcomments’.

Sub AddThreadedComment()

Sub End

We will define those variables which we defined for add comments like for worksheet, Range, and commentthreaded.

Sub AddThreadedComment()
Dim thCmt As CommentThreaded
Dim WS As Worksheet
Dim Rng As Range

End Sub

Now we will set the worksheet .

Sub AddThreadedComment()
Dim thCmt As CommentThreaded
Dim WS As Worksheet
Dim Rng As Range

Set WS = ActiveSheet
End Sub

Give the range of the cell in which the comments are to be added (“D3”).

Sub AddThreadedComment()
Dim thCmt As CommentThreaded
Dim WS As Worksheet
Dim Rng As Range

Set WS = ActiveSheet
Set Rng = WS.Range("D3")
End Sub

Now we will use ClearComments to clear the range.

Sub AddThreadedComment()
Dim thCmt As CommentThreaded
Dim WS As Worksheet
Dim Rng As Range

Set WS = ActiveSheet
Set Rng = WS.Range("D3")

Rng.ClearComments
End Sub

Now we will set cmt then use ‘AddCommentThreaded‘ function with rng,  In the parameter of Add Comment Threaded we will write comments text.

Sub AddThreadedComment()
Dim thCmt As CommentThreaded
Dim WS As Worksheet
Dim Rng As Range

Set WS = ActiveSheet
Set Rng = WS.Range("D3")

Rng.ClearComments
Set thCmt = Rng.AddCommentThreaded("Please put a date")
End Sub

Run the code then go to the worksheet Comments have been added to the cell

After this we will work to add reply along with comment. with it we will add reply text.

Sub AddThreadedComment()
Dim thCmt As CommentThreaded
Dim WS As Worksheet
Dim Rng As Range

Set WS = ActiveSheet
Set Rng = WS.Range("D3")

Rng.ClearComments
Set thCmt = Rng.AddCommentThreaded("Please put a date")
thCmt.AddReply "please put date in ""dd-mm-yyyy"" format"
End Sub

Write the code and run it, then go to the worksheet, here in the worksheet, along with the comment, the reply has also been added.

So, in this way we can add comments to the cell.

So, I hope you have understood How to add comments in cell using Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.

DOWNLOAD USED EXCEL FILE FROM HERE>>

LEARN MORE TOPIC IN VBA HERE

You can also see well-explained video here about How to add comments in cell using Excel VBA