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.
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.
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 .
And then Define variable for comment, worksheet, and range.
Now we will set the worksheet
Give the range of the cell in which the comments are to be added (“D3”).
Now we will use ClearComments to clear the range.
Now we will set cmt then use ‘AddComments‘ function with rng, In the parameter of Add comment we will write comments text.
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.
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’.
We will define those variables which we defined for add comments like for worksheet, Range, and commentthreaded.
Now we will set the worksheet .
Give the range of the cell in which the comments are to be added (“D3”).
Now we will use ClearComments to clear the range.
Now we will set cmt then use ‘AddCommentThreaded‘ function with rng, In the parameter of Add Comment Threaded we will write comments text.
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.
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
Related Posts
Data Entry with Option Button by UserForm in Excel VBA Easily (2 Frame)
How to use String Functions (Left, Right and Mid) in Excel VBA Easily (3 functions)
How to use DateValue Function in Excel VBA Easily (7 Examples)