Comments

This example adds a comment to cell E5 on worksheet one.
This line will generate an error if the cell already contains a comment.

Worksheets(1).Range("E5").AddComment "Current Sales" 

The AddComment method can only be applied to a single cell Range.
If the range object refers to more than one cell, then a run-time error will be occur.


Comments Collection

Dim colComments As Comments 
Dim objComment As Comment

Set colComments = Worksheets("Sheet1").Comments
Set colComments = Sheets(1).Comments
Set colComments = ActiveSheet.Comments

You cannot assign names to comments so the only way to refer to them in the collection is by there index number.
All collections are 1 based, so this refers to the first comment on the worksheet.

Set objComment = colComments(1) 

You can also refer to them directly using the Range object which the comment is attached to

Set objComment = Range("D2").Comment 

Does a Comment Exist ?

If Not (Range("A1").Comment Is Nothing) Then 
'then there is a comment
End If

Comment Properties & Methods

objComment.Application  'Returns the name of the application that created the comment, in this case Excel  
objComment.Author 'Returns the name of the person who created the comment
objComment.Parent 'Returns the parent object for the comment, in this case a range
objComment.Shape 'Returns the shape object that represents the shape attached to the comment
objComment.Visible 'Returns whether the comment is displayed or not

objComment.Text 'Returns the text that is displayed inside the comment  
objComment.Delete 'Deletes the comment from the worksheet
Set objComment = objComment.Next 'Returns the comment object that represents the next comment
Set objComment = objComment.Previous 'Returns the comment object that represents the previous comment

Changing the Shape of the Comment


Dim objComment As Comment 
For Each objCell in ActiveSheet.Comments

   objComment.Shape.TextFrame.HorizontalAlignment = xlLeft
   objComment.Shape.TextFrame.VerticalAlignment = xlTop
   objComment.Shape.TextFrame.AutoSize = False
   objComment.Shape.TextFrame.MarginLeft =
   objComment.Shape.TextFrame.MarginRight =
   objComment.Shape.TextFrame.MarginTop =
   objComment.Shape.TextFrame.MarginBottom =

   objComment.Shape.Fill.ForeColor.RGB = RGB(10,10,10)

Next objCell

It is possible to change the shape of the comment textbox

ActiveCell.CommentShape.AutoShape.Type = msoAutoShapeType.msoShape16PointStar 


For all the comments on the active worksheet

For Each objComment In ActiveSheet.Comments 
   Call MsgBox(objComment.Text)
Next objComment

Which cell has the first comment

Call MsgBox ActiveSheet.Comments(1).Parent.Address 

Adding some comments to the current selection

The AddComment method will generate an error if the cell already contains a comment.

Dim objCell As Range 
For Each objCell in Selection
   objCell.AddComment "your comment text"
   objCell.Comment.Visible = True
   objCell.Comment.Text = "your comment text"
Next objCell

Remove all the comments from the current selection

Selection.ClearComments 

Important

objComment.Creator 'Not used in Windows (only Macintosh)  

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrev