Deleting Drawing Objects (within a range)

[andrewm]

Wondering how you would go about automating the deletion of a number of 'drawing objects' within a worksheet.

I have previously used the following line of VB successfully:

ActiveSheet.DrawingObjects.Delete

but need to protect a couple of image objects on the page from deletion.

Thinking out loud I wondered whether protecting the range of cells they sit over is an option of whether simply directing the above function or similar to a range other than one that includes theses images.

Any advice would as always be appreciated.

 

[Damon Ostrander]

Hi andrewm,

There are a number of possible ways to identify the drawing objects that you want to protect from deletion. Regarding basing it on the underlying cell protection status, this would certainly work, but to make it easy I would recommend basing it just on the cell under the top left corner of the drawing since drawings (Shape objects) have a TopLeftCell property that identifies that cell.

But another and probably even easier method is to identify those drawings that are protected via a naming convention for the drawing. For example, just append an "_P" to the name of every drawing you want to protect. Then when you loop through all the drawing objects to delete them, don't delete the ones with names like "PlanView_P". This way the drawing will not be accidentally deleted if they get moved over cells that are not protected.

Damon

 

[andrewm]

Protecting Drawing objects

Thanks for the feedback,

But another and probably even easier method is to identify those drawings that are protected via a naming convention for the drawing. For example, just append an "_P" to the name of every drawing you want to protect. Then when you loop through all the drawing objects to delete them, don't delete the ones with names like "PlanView_P". This way the drawing will not be accidentally deleted if they get moved over cells that are not protected.

But as I'm new to VB I'm a little unsure how to go about putting together some code to delete only those objects without the _P monkier attached. Would very much appreciate some advice on this.

 

[Damon Ostrander]

Hi again Andrew,

The following code snippet will delete all shapes except those with "_P" at the end of their names on the active worksheet:

Dim Sh As Shape
For Each Sh In Activesheet.Shapes
If Not Sh.Name Like "*_P" Then Sh.Delete
Next Sh

You can name a shape by selecting it and typing the name in the Name box just above cell A1 (same as you would name a cell).

Damon

 

[andrewm]

Copying...

Damon,

Many thanks for your help, you're suggestion was just what I was looking for.

If only I could trouble you for one more thing.

I'm having some difficulty in copying data and drawing object elements from one workbook worksheet to another.

I have been using the following with rather limited success. By limited I mean the data in cells copies across faultlessly each time, however often drawing objects are left behind.

Sub US_Maker()

With Application
.ScreenUpdating = False
.DisplayAlerts = False

Workbooks.Open "C:\Reporting\2005\02_2005\US\US_FEB_05.xls"
Workbooks("US_FEB_05.xls").Worksheets("US - Sum").Cells.Copy _
Workbooks("Master.xls").Worksheets("USA Summary").Cells
Workbooks("US_FEB_05.xls").Close False

.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub


Any suggestion you might have here as always would be greatly appreciated.


Kind Regards,

Andrew

 

[Damon Ostrander]

Hi Andrew,

It looks like your intent is to copy the entire worksheet, cells, shapes, formatting, etc. If this is the case you can simply switch to a worksheet copy, which in your case would look like this:

Workbooks("US_FEB_05.xls").Worksheets("US - Sum").Copy _
before:=Workbooks("US_FEB_05.xls").Worksheets(1)
ActiveSheet.Name = "USA Summary"

This inserts the sheet as the first tab in the destination workbook.

In your code you already had a sheet named "USA Summary", but I assumed this was a blank sheet you had placed there to receive the copied data, and there will no longer be a need for this sheet. But if the sheet must be there and therefore will conflict with the new sheet, you must delete it before adding the USA Summary sheet thus:

Workbooks("US_FEB_05.xls").Worksheets("USA Summary").Delete