Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Excel Comments RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Is it possible to run a macro or some kind of command in Excel 2007 to
    automatically adjust all comment boxes sizes to just the size needed to fit
    the comments if you see what I mean. There are some comments that even though
    there is only a few words the actual comment boxes stretches down over 50,000
    cells - not sure how the users done this.

     

    Thanks 

    Friday, January 23, 2009 12:05 PM

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
     

    Hello,

     

    Thank you for your post!  Unfortunately there is not a current forum to post the question you are asking so I would recommend that you post you question to one of the discussions in microsoft.public.excel.programming located here: 

    (http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.excel.programming&cat=en_us_19e6ab45-8feb-4ce1-9a70-1d87d8dfb131&lang=en&cr=us)

    Hope that would be helpful.

    Hope that helps!

    Thanks & regards,

    Ashawani Tier 2 Application Support Server and Tools Online Engineering Live Services Team
    Tuesday, January 27, 2009 5:04 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Use a macro like this:

    Sub comment_autosizer()
    Dim celle As Range
    For Each celle In Sheet1.UsedRange
        celle.Comment.Shape.TextFrame.AutoSize = True
    Next celle
    End Sub

    That will deal with Sheet1 but if you want it to Autosize the Comment boxes of every Comment in the workbook you could use:

    Sub comment_autosizer()
    Dim ws As Worksheet
    Dim celle As Range

    For Each ws In Worksheets
        For Each celle In ws
            celle.Comment.Shape.TextFrame.AutoSize = True
        Next celle
    Next ws

    End Sub

    Thursday, April 23, 2009 10:59 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Note the first simple macro:

    Sub comment_autosizer()
    Dim celle As Range
    For Each celle In Sheet1.UsedRange
        celle.Comment.Shape.TextFrame.AutoSize = True
    Next celle
    End Sub

    works well - but the 2nd one is not reliable.
    Thursday, April 23, 2009 11:03 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    This seems to be more robust as it can cope with worksheets that are empty:

    Sub comment_autosizer()
    Dim ws As Worksheet
    Dim celle As Range

    For Each ws In Worksheets
        On Error Resume Next
        For Each celle In ws.UsedRange
            celle.Comment.Shape.TextFrame.AutoSize = True
        Next celle
    Next ws

    End Sub

    so it should deal with a whole workbook without 'erroring' so to speak.

    Thursday, April 23, 2009 11:32 AM