Adding comments with VBA is somehow quite a trivial task, if you already have the text of the comment and you are allowed to delete the comment if it is present.
In general, the only difficult part is the check, whether the cell has a comment or not and it is done with a simple if condition:
If myCell.Comment.text <> "" Then . If there is a comment, which is bigger than “”, then there is a filled out value in the comment.
However, as far as in the current example we are going to put a comment anyway, then the check is even not needed.
myCell.ClearComments is going to clear any comment, independently whether it exists or not.
The code below loops through the cells in the range
A1:Z20 and adding a comment to every cell in the range. To make it different, the
Chr() function is used, producing signs from the ASCII table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Public Sub AddCommentsEverywhere() Dim commentText As String Dim myCell As Range Dim chrCount As Long: chrCount = 65 For Each myCell In Worksheets(1).Range("A1:Z20") If chrCount > 65 + 26 * 2 Then chrCount = 65 myCell.ClearComments myCell.AddComment Chr(chrCount) myCell = chrCount & " " & Chr(chrCount) chrCount = chrCount + 1 Next myCell End Sub |
That’s all, folks! 🙂
Add comments in Excel with VBA
Adding comments with VBA is somehow quite a trivial task, if you already have the text of the comment and you are allowed to delete the comment if it is present.
In general, the only difficult part is the check, whether the cell has a comment or not and it is done with a simple if condition: If myCell.Comment.text <> "" Then . If there is a comment, which is bigger than “”, then there is a filled out value in the comment.
However, as far as in the current example we are going to put a comment anyway, then the check is even not needed. myCell.ClearComments is going to clear any comment, independently whether it exists or not.
The code below loops through the cells in the range A1:Z20 and adding a comment to every cell in the range. To make it different, the Chr() function is used, producing signs from the ASCII table:
That’s all, folks! 🙂
Related posts: