Add a comment (XL2000 SP3)

Topic

New Reply

How do I detect if a cell contains a comment? I want to add a comment to the cell if it does not already contain one.

VBA help says that the syntax for setting a comment’s text is:

Worksheets(1).Range(“E5”).Comment.Text “reviewed on ” & Date

However when I record a macro it comes up with:

Range(“I11″).Comment.Text Text:=”IS:” & Chr(10) & “”

What is the reason for the extra Text:= in the recorded macro?

Reply | Quote

Replies

Something like this?

Option Explicit
Sub SampleAddComment()
  Dim cmt As Comment
  Dim rCell As Range
  Set rCell = Worksheets(1).Range("E5")
  Set cmt = rCell.Comment
  If cmt Is Nothing Then
    rCell.AddComment
    rCell.Comment.Text "reviewed on " & Date
  End If
End Sub

The “Text:=” is an explicit declaration of the parameter being used. It is not required since Text is the first parameter…

Steve

Reply | Quote

In Visual Basic, you can specify arguments in two ways: by position or by name.

Here is an example of using by position arguments:

Range("A1:D100").Sort Range("A1"), xlAscending, , , , , ,xlYes

The arguments aren’t named, so the must be exactly in the order specified in the VBA help, and omitted arguments must be indicated by commas if they occur between used arguments.
Here is the same instruction using named arguments:

Range("A1").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes

or

Range("A1").Sort Header:=xlYes, Order1:=xlAscending, Key1:=Range("A1")

Since the arguments are specified by name, their order doesn’t matter, and omitted arguments are simply, er, omitted.

Comment.Text has three arguments named Text, Start and Overwrite. Your first example specifies the Text argument by position, the second one by name.

Reply | Quote

[indent]

---

Comment.Text has three arguments named Text, Start and Overwrite.

---

[/indent]

Hans, I could not find any reference to these three arguments in Help at all. (or in relation to .AddComment) (It could be very useful for something I am working on at present)

Could you please elaborate or point me to an article?

Many thanks.

Reply | Quote

See the Text method of the Comment method in the VBA help, or in the Object Browser, or see Text Method [Excel 2003 VBA Language Reference] and expand “Text method as it applies to the Comment object.”

Reply | Quote

Also see the thread starting at post 58,936.

Reply | Quote

Thanks to everyone for your help.

I am having trouble now in concatenating text and date whilst using the overwrite option; suppose I want to add “Cell updated on” and the date and also tell VBA not to overwrite the existing comment, what should the syntax be?

su

Reply | Quote

You can use

Range("I11").Comment.Text Text:=vbLf & "Cell updated on: " & Date, Start:=20000, Overwrite:=False

The number 20000 is an arbitrary number higher than the expected length of the comment. Excel VBA doesn’t mind if it’s higher than the actual length.

Reply | Quote