Results 1 to 4 of 4

Thread: [RESOLVED] Excel VBA - Is it possible make a cell comment auto width and height?

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2006
    Posts
    14

    Resolved [RESOLVED] Excel VBA - Is it possible make a cell comment auto width and height?

    Hi.

    When I insert a cell comment using VBA, is it possible
    to make it auto adjust the width and height according to the content?

    If one comment consist of let say 10 lines and the longest line is of 45 characters then those values should direct the height and width of the comment...

    Any trick to make the size fit the content would be nice.

  2. #2
    eltiT resU motsuC Static's Avatar
    Join Date
    Oct 2000
    Location
    Rochester, NY
    Posts
    9,390

    Re: Excel VBA - Is it possible make a cell comment auto width and height?

    I could not figure out how to autosize it....

    Autosize is for a textframe and autofit is for a range...
    I think u will have to make a guess really...
    In VB u can use TextWidth.. but VBA does not have it.

    VB Code:
    1. Range("A5").AddComment
    2.     With Range("A5").Comment
    3.         .Visible = True
    4.         .Text Text:="Static:" & Chr(10) & "test" & Chr(10) & ""
    5.         .Shape.ScaleWidth 1.9, msoFalse, msoScaleFromTopLeft
    6.         .Shape.ScaleHeight 1.8, msoFalse, msoScaleFromTopLeft
    7.     End With
    JPnyc rocks!! (Just ask him!)
    If u have your answer please go to the thread tools and click "Mark Thread Resolved"

  3. #3

    Thread Starter
    New Member
    Join Date
    Jun 2006
    Posts
    14

    Re: Excel VBA - Is it possible make a cell comment auto width and height?

    Hi and thanks for your reply!

    Did some more testing after I read your reply and found this:

    If I USE:

    .Shape.TextFrame.AutoSize = True

    and REMOVE these two lines:

    .Shape.ScaleWidth 1.9, msoFalse, msoScaleFromTopLeft
    .Shape.ScaleHeight 1.8, msoFalse, msoScaleFromTopLeft

    Then it look like working..... supercool
    Looks like chr(10) and the longest line dictates the with, which is what I was looking for.

    Thanks for your feedback

  4. #4
    eltiT resU motsuC Static's Avatar
    Join Date
    Oct 2000
    Location
    Rochester, NY
    Posts
    9,390

    Re: [RESOLVED] Excel VBA - Is it possible make a cell comment auto width and height?

    ahhh.. good catch.. TextFrame cool
    JPnyc rocks!! (Just ask him!)
    If u have your answer please go to the thread tools and click "Mark Thread Resolved"

Posting Permissions

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



Click Here to Expand Forum to Full Width