+ Reply to Thread
Results 1 to 5 of 5

Changing the size of Comment Shapes

  1. #1
    Randy Harris
    Guest

    Changing the size of Comment Shapes

    I need to change the size on a great many comments on a Worksheet. I find
    that this code:

    Selection.ShapeRange.ScaleHeight 1.6, msoFalse, msoScaleFromTopLeft

    will change, but relative to the default size. I can't find the syntax to
    change to Height and Width settings.

    Also, how can I change the default size?

    Thanks for any help.


  2. #2
    Tom Ogilvy
    Guest

    Re: Changing the size of Comment Shapes

    Sub ac()
    Range("B9").Comment.Shape.Height = 40
    Range("B9").Comment.Shape.Width = 80
    End Sub


    --
    Regards,
    Tom Ogilvy




    "Randy Harris" <randy@nospam.com> wrote in message
    news:OaO81vy0FHA.560@TK2MSFTNGP12.phx.gbl...
    > I need to change the size on a great many comments on a Worksheet. I find
    > that this code:
    >
    > Selection.ShapeRange.ScaleHeight 1.6, msoFalse, msoScaleFromTopLeft
    >
    > will change, but relative to the default size. I can't find the syntax to
    > change to Height and Width settings.
    >
    > Also, how can I change the default size?
    >
    > Thanks for any help.
    >




  3. #3
    Chip Pearson
    Guest

    Re: Changing the size of Comment Shapes

    Randy,

    Try something like

    Dim CMT As Comment
    Set CMT = ActiveSheet.Comments(1)
    CMT.Shape.Height = 100
    CMT.Shape.Width = 200

    I don't believe you can change the default size.


    --
    Cordially,
    Chip Pearson
    Microsoft MVP - Excel
    Pearson Software Consulting, LLC
    www.cpearson.com




    "Randy Harris" <randy@nospam.com> wrote in message
    news:OaO81vy0FHA.560@TK2MSFTNGP12.phx.gbl...
    >I need to change the size on a great many comments on a
    >Worksheet. I find
    > that this code:
    >
    > Selection.ShapeRange.ScaleHeight 1.6, msoFalse,
    > msoScaleFromTopLeft
    >
    > will change, but relative to the default size. I can't find
    > the syntax to
    > change to Height and Width settings.
    >
    > Also, how can I change the default size?
    >
    > Thanks for any help.
    >




  4. #4
    Randy Harris
    Guest

    Re: Changing the size of Comment Shapes


    "Tom Ogilvy" <twogilvy@msn.com> wrote in message
    news:%2387853y0FHA.2932@TK2MSFTNGP10.phx.gbl...
    > Sub ac()
    > Range("B9").Comment.Shape.Height = 40
    > Range("B9").Comment.Shape.Width = 80
    > End Sub
    >
    >
    > --
    > Regards,
    > Tom Ogilvy


    Thanks. You make it seem so simple. I couldn't figure it out. I must have
    some sort of mental block or something.

    Again, thanks,
    Randy Harris


  5. #5
    David McRitchie
    Guest

    Re: Changing the size of Comment Shapes


    Hi Randy,
    Another option is a close fitting comment.

    Resizing All Cell Comments (#resizing)
    http://www.mvps.org/dmcritchie/excel...t.htm#resizing

    The following may be a fixup if you have serious problems for size of the comment box. (Format, Alignment, Automatic size). .

    Sub FitComments()
    Dim c As Comment
    For Each c In ActiveSheet.Comments
    c.Shape.TextFrame.AutoSize = True
    Next c
    End Sub
    ---
    HTH,
    David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
    My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
    Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

    "Randy Harris" <randy@nospam.com> wrote in message ..
    > I need to change the size on a great many comments on a Worksheet.




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1