Resizing Comments in Excel using ShapeRange

Question

I have a spreadsheet that is created programatically with a lot of comments in it(up to 40,000). The comments get resized after deleting several columns from the worksheet. This is apparently a bug in excel. ( http://answers.microsoft.com/en-us/office/forum/office_2007-excel/excel-comment-boxes-resizing-themselves-andor/3fdf3e72-6ca5-4186-a656-b7b6fd8db781?msgId=d55534a5-4603-482e-ac97-9ec260124f78 )

Ideally I would like to AutoSize all the comments at once after deleting the columns.

Attempting to avoid looping through each individual comment, here's what I've tried so far.

• Setting AutoShapeDefaults has no effect - the comments still get resized after deleting the columns.
• XlPlacement property. XlMove and XLMoveAndSize have no effect.
• Worksheet.Shapes.SelectAll throws an OutOfMemory Exception no matter the amount of comments

My thought is to get a ShapeRange object of all the comments in the spreadsheet and set the size from there.

This works perfectly:

        public static void ResizeComments()
        {
        Microsoft.Office.Interop.Excel.Workbook objWorkbook;
        objWorkbook = (Workbook)Globals.ThisAddIn.Application.ActiveWorkbook;
        Worksheet objSheet = (Worksheet)objWorkbook.ActiveSheet;

        int[] test = {1,2,3,4,5};
        ShapeRange sRange = objSheet.Shapes.Range[test];
        sRange.Height = 100;
        sRange.Width = 220;
        }

Changing to this throws an exception "Exception from HRESULT: 0x800A03EC" at the AutoSize line.

        ShapeRange sRange = objSheet.Shapes.Range[test];
        sRange.TextFrame.AutoSize = true;

Using my actual array of Shape Indices throws the same exception but at Shapes.Range[]. I've looked at the shapes variable while debugging and it's identical to test except it's int[249] instead of int[5];

        int[] shapes = (int[])shapes.ToArray(typeof(int)); 
        ShapeRange sRange = objSheet.Shapes.Range[shapes];

Seamanship

Perhaps this should be an answer, but it's not an answer, so comments: ShapeRange.TextFrame is read-only so to apply autosize you will have to loop through the array. I'm assuming there must be something wrong with int[] shapes = (int[])shapes.ToArray(typeof(int)); Because I attempted to replicate your method in VBA and could successfully generate a ShapeRange of 50000 comments at once. However, if I attempt to select 50001 (with only 50000 shapes) it fires the same error as using AutoSize. Make sure the array is valid. – Anciently

Answer 1

well I'll answer with VBA code that has to run from within a module in Excel. From discussion and answer here.

Sub CommentFixer()
Dim Arng As Range, Acl As Variant, InitRng As Variant, MaxSize
Set InitRng = Selection
Set Arng = Application.InputBox("Select Ranges", , , , , , , 8)

For Each Acl In Arng
    If (Not (Acl.Comment Is Nothing)) And (Acl.MergeArea.Count = 1) Then
        Acl.Select
        Selection.Comment.Visible = True
        Selection.Comment.Shape.TextFrame.AutoSize = True
        'Commented as is obsolete if no further processing is needed  
        'Selection.Comment.Shape.Select
        'Commented not to fix Comment Aspect Ratio
        'With Selection.ShapeRange       'Fix 2.5 aspect ratio
        '    .LockAspectRatio = msoFalse
        '    MaxSize = .Width / 2.5
        '    If MaxSize > .Height Then
        '        .Height = MaxSize
        '    Else
        '        .Width = .Height * 2.5
        '    End If
        'End With
        'Commented to neglect fonts
        'With Selection.Font
        '    .Bold = False
        '    .Name = "Times New Roman"
        '    .Size = 12
        'End With

        Acl.Comment.Visible = False
    End If
Next
InitRng.Select

End Sub

kept the code and commented items not needed. I still have to make-up for merged cells that cannot be handled yet.

cheers

Buie