Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add a comment (XL2000 SP3)

    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?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Add a comment (XL2000 SP3)

    Something like this?

    <pre>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</pre>


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

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add a comment (XL2000 SP3)

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

    Here is an example of using by position arguments:
    <code>
    Range("A1100").Sort Range("A1"), xlAscending, , , , , ,xlYes
    </code>
    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:
    <code>
    Range("A1").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
    </code>
    or
    <code>
    Range("A1").Sort Header:=xlYes, Order1:=xlAscending, Key1:=Range("A1")
    </code>
    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add a comment (XL2000 SP3)

    Also see the thread starting at <post:=58,936>post 58,936</post:>.

  5. #5
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add a comment (XL2000 SP3)

    <hr>Comment.Text has three arguments named Text, Start and Overwrite.<hr>

    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.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add a comment (XL2000 SP3)

    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."

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add a comment (XL2000 SP3)

    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add a comment (XL2000 SP3)

    You can use
    <code>
    Range("I11").Comment.Text Text:=vbLf & "Cell updated on: " & Date, Start:=20000, Overwrite:=False
    </code>
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •