Answered by:
Excel Comments

Question
-
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
-
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:
Hope that would be helpful.
Hope that helps!
Thanks & regards,
Ashawani Tier 2 Application Support Server and Tools Online Engineering Live Services Team- Proposed as answer by ashawani_dubey Tuesday, January 27, 2009 5:04 AM
- Marked as answer by ashawani_dubey Monday, March 9, 2009 6:54 AM
Tuesday, January 27, 2009 5:04 AM
All replies
-
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:
Hope that would be helpful.
Hope that helps!
Thanks & regards,
Ashawani Tier 2 Application Support Server and Tools Online Engineering Live Services Team- Proposed as answer by ashawani_dubey Tuesday, January 27, 2009 5:04 AM
- Marked as answer by ashawani_dubey Monday, March 9, 2009 6:54 AM
Tuesday, January 27, 2009 5:04 AM -
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 RangeFor Each ws In Worksheets
For Each celle In ws
celle.Comment.Shape.TextFrame.AutoSize = True
Next celle
Next wsEnd Sub
Thursday, April 23, 2009 10:59 AM -
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 -
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 RangeFor Each ws In Worksheets
On Error Resume Next
For Each celle In ws.UsedRange
celle.Comment.Shape.TextFrame.AutoSize = True
Next celle
Next wsEnd Sub
so it should deal with a whole workbook without 'erroring' so to speak.Thursday, April 23, 2009 11:32 AM