Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there a way (perhaps via VBA) to autosize all comments in a spreadsheet?

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Re: Autosize comments...
    There is no built-in way to do that as far as I know.

    Vba code could save the text and cell location for each comment, delete all comments and then add the comments back to the worksheet.
    Whether the comment size would be to your liking is uncertain.

    VBA code can size (not autosize) comments to a specific width and height. Something like...
    '--
    Set cmts = Worksheets(1).Comments
    For Each c In cmts
    c.Shape.Width = 70
    c.Shape.Height = 120
    Next
    '--
    Jim Cone
    Portland, Oregon USA
    Primitive Software Excel add-ins

  3. #3
    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
    Something like this:
    Code:
    for each cmt in activesheet.comments
        cmt.shape.textframe.autosize = true
     next cmt
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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