Excessive Workbook size - hidden text boxes

[grd]

Hi I have a simple workbook which has been passed to me which is 5MB in size and comprises about 4 pages of printed info over 3 worksheets.

The range of data (simple text and simple formulae) only extends to the visible data, there are no graphics or ranges, no sharing and no formatting (because I deleted it all).

I have created a new workbook by copying and pasting but still 5MB.

By trial and error, I have isolated a small area of 20 rows and 10 columns which create a new workbook of over 1MB. However, when copying one row at a time the size is only 14KB. It is only when I copy the range that the size jumps.

After quite a bit of investigation, it would appear I have a large number of hidden text boxes as a result of someone continually adding them in and then deleting the rows either side thereby leaving an invisible text box with no way of selecting it other than tabbing from a newly created visible text box.

Could anyone suggest a way of deleting all these boxes? I have tried recording a macro (the limit of my macro expertise!) but each box appears to have an individual name and so a general macro will not work.

Any help would be much appreciated.

Regards

Graham

 

[Andrew Poulsom]

Do you know where the TextBoxes are from - Control Toolbox or somewhere else?

 

[grd]

Wow - quick response.

They are merely text boxes inserted by the user to show some information in the spreadsheet and which he assumed had been deleted during the action of deleting the range of rows above and below.

It was only after extensive testing by me that discovered the problem.

I assume they have been created by using the "text box" icon from the "drawing" toolbar. At least that is what I used to recreate the problem.

 

[nbrcrunch]

this deletes all drawing objects from sheet.

Sub DelAllDraw()
' this one deletes all objects
Worksheets("Sheet1").DrawingObjects().Delete
End Sub

 

[grd]

Thanks nbrcrunch. It works a treat after inserting actual worksheet names.

Is there any way to select all worksheets no matter what their name?

 

[Andrew Poulsom]

This will delete only the TextBoxes:

Sub Test()
    Dim Sh As Shape
    For Each Sh In ActiveSheet.Shapes
        If Sh.Type = msoTextBox Then
            Sh.Delete
        End If
    Next Sh
End Sub