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

    Comments autosize doesn't stick (ExcelXP)

    Have sheet with a lot of comments. Frequently my code hides and shows columns to adjust to selections the users make. To allow all to work, I have to make sure that all my comments have "move-and-size-with-cells" enabled. Although that doesn't seem to be the default setting, I make that happen via the code that triggers when the user makes a different selection (see a separate thread some weeks ago).

    Now I found out there's another -more odd- thing going on... it also appears that the Comment - "Automatic-Size" setting doesn't stick.... I want that enabled but at random moments this seems to disable by itself. Here things seem more complex as also from code I do not seem to be able to control this fully: I do have the right code but it either doesn't always work and/or sometimes the setting disappears... Anyone who can tell me how I can enable this so it always sticks?? Could this also have something to do with me hiding and showing columns??

    Thanks,

    EJ

  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: Comments autosize doesn't stick (ExcelXP)

    Check out <post#=318836>post 318836</post#> for some code to run to make all comments move and size.

    You can add this to the code to "AutoSize"
    <pre>cmt.Shape.TextFrame.AutoSize = True</pre>


    If you do you will convert the Move and Size to just Move, since you told excel to autosize!

    You can not "Move and Size" and do "AutoSize" they are mutually exclusive! The latest one will take:
    If Autosized and then move/Size picked, autosize removed
    If move/size and then autosized, converted to just move

    Steve

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

    Re: Comments autosize doesn't stick (ExcelXP)

    Here's a new problem that is now happening to my sheet.....

    Sometimes, when my code hides columns, I get an error that the column cannot be hidden. After some manual checking, I found out this is caused by a comment in a cell somewhere in a columne before the current one that is so wide that it would run off the screen.

    So... I changed my code and added:

    If oCmt.Shape.Width > 500 Then oCmt.Shape.Width = 500

    Now, however when I look at the comment it is limited in width but (obviously) the HEIGHT is not autosizing to display the whole comment... it's like a can of worms here... any idea how I can tackle this one???

    Thanks,

    Erik Jan

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Comments autosize doesn't stick (ExcelXP)

    Hi Erik,

    The TextFrame.AutoSize property autosizes a comment to fit everything on one line. There isn't a property to fix the width and vary the height. Here's a snippet of code I use:

    With .Comment.Shape
    'automatically resizes the comment
    .TextFrame.AutoSize = True
    'position the comment adjacent to its cell
    If TargetCell.Column < 254 Then.IncrementLeft -11.25
    If TargetCell.Row <> 1 Then .IncrementTop 8.25
    End With

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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