excel crashing after deleting shapes

[slarbie]

Recently I've had two different files crash my excel consistently under these
conditions.
Used some code to copy sheets to a new workbook, then this segment:

Dim shp as Shape
For Each Shp In ActiveSheet.Shapes
Shp.Delete
Next Shp

After the sub ends and I try to save the new workbook, Excel is crashing
every time. One of the sheets I'm using this for has enough shapes on it
that I don't want to make a line naming each one to delete. How else can I
skin this cat?

 

[Peter T]

Can't see an obvious reason why that code should cause Excel to crash
subsequently on saving. You could try simply -

ActiveSheet.DrawingObjects.Delete

I can only assume there is something else wrong with your file, other code,
or something particular about the shapes you are deleting. Any code in the
Workbook_BeforeSave event getting confused?

Regards,
Peter T

 

[Bob Phillips]

Not sure, but it may be linked to deleting all shapes, including built-in
shapes. See a solution that Dave Peterson and I developed for a controlled
deletion of shapes at http://tinyurl.com/g2tsl

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[slarbie]

Thanks guys! I'll go off and play with this "aha!" moment that I need to
distinguish between the kinds of shapes I'm deleting. The sheet has a pivot
table and I'm thinking maybe some of the functional parts of the pivot were
getting deleted, like dropdown boxes for selecting fields. Then maybe trying
to save a crippled pivot table could get the application confused enough to
flip it out.

 

[slarbie]

Thanks Bob - this was the more helpful reply. I really only have one shape
type in the sheet that needs deleting, so will go figure out that type's
proper excel name and add an if/then to test each one before deleting. I
think my code may have deleted essential pieces of a pivot table, and made it
'unsavable'.

Barb B.

 

[Peter T]

This would fall in the category "something particular about the shapes you
are deleting" !

To avoid deleting drop downs look at the code Bob posted in message #6 in
the thread he referred you to.

Alternatively if you only have one type of shape to delete maybe one of the
following

Activesheet.Rectangles.delete
Activesheet.Lines.delete
Activesheet.Ovals.delete

and various other types at the "drawingobject" level.

Regards,
Peter T