Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Edit cell comments (EXCEL 97/2000)

    I need to add a comment to a cell. Then I want to format the comment (put it on a green bg) and make it AUTOSIZE.... (in VBA of course)

    I 'recorded' this but when I try to DO it from VBA I get errors (not during compile).... Anyone with a little code-snippet that does the job... (believe it... especially the autosize does not seem to be supported although it is part of the recorded code...).

    Or am I doing something completely wrong here???

    Thanks!

    Erik Jan

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Edit cell comments (EXCEL 97/2000)

    Hi,
    I'm not sure what you mean by Autosize - do you mean the Move and Size with cells property?
    Anyway, this code ought to point you in the right direction:
    <pre> With Selection
    .addcomment
    With .Comment
    .Visible = False
    .Text Text:="Rory Archibald:" & Chr(10) & "This is a test"
    With .Shape
    With .Fill
    .Visible = msoTrue
    .Solid
    .ForeColor.SchemeColor = 42
    .Transparency = 0#
    End With
    With .Line
    .Weight = 0.75
    .DashStyle = msoLineSolid
    .Style = msoLineSingle
    .Transparency = 0#
    .Visible = msoTrue
    .ForeColor.RGB = RGB(0, 0, 0)
    .BackColor.RGB = RGB(255, 255, 255)
    End With
    .Placement = xlMoveAndSize
    End With
    End With
    End With
    End Sub
    </pre>

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Edit cell comments (EXCEL 97/2000)

    Thanks Rory,

    I could get that far... on the autosize:

    Format-comment, then Alignment-tab then Automatic Size.

    If I record this I get:

    Range("A100").Select
    Range("A100").Comment.Text Text:= "Test"
    With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlTop
    .Orientation = xlHorizontal
    .AutoSize = True
    End With

    Question is WHERE do I fit in that .AutoSize statement in your (working) code??? (Basically, I want the comment not to fold on me when the text is long)

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Edit cell comments (EXCEL 97/2000)

    Oh OK. You want the Shape's TextFrame object which has an autosize property.
    In my code you could add
    .textframe.autosize = true
    underneath the
    .placement = xlmoveandsize
    line. The textframe also has those alignment and orientation properties if you want to set those.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Edit cell comments (EXCEL 97/2000)

    Thanks Rory... that did it... <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    Strangely enough the "textframe" never showed-up with me when I recorded...

    Erik Jan

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Edit cell comments (EXCEL 97/2000)

    That's one of the great things about the macro recorder - it doesn't always record the fact that you've selected a new object! It uses the Selection object but makes no record of the fact that you had actually selected the textframe. Ours not to reason why, I suppose.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Edit cell comments (EXCEL 97/2000)

    <img src=/w3timages/blackline.gif width=33% height=2>
    > "textframe" never showed-up
    <img src=/w3timages/blackline.gif width=33% height=2>

    Excel usually cheats by magically using the selection. Here's a summary of the minimum that needs to be done:
    <pre>Option Explicit
    Sub Demo()
    With ActiveCell
    .AddComment
    .Comment.Visible = False
    .Comment.Text Text:="My two cents!"
    .Comment.Shape.TextFrame.AutoSize = True
    .Comment.Shape.Fill.ForeColor.RGB = vbGreen
    .Next.Select
    End With
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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