Macro to delete autoshapes depending on their colour

[simonansell]

Hi,

I have a worksheet with lots of auto shapes and wish to be able to delete specific ones depending on their colour and leave the remaining shapes untouched.

 

[sulakvea]

welcome to the board

Sub DeleteThoseShapes()
For Each MyShp In Sheet1.Shapes
 If MyShp.Fill.ForeColor.SchemeColor = 16 Then MyShp.Delete
Next
End Sub

Change Sheet1 and color index to suit yoru needs

 

[simonansell]

Thanks for the code.

When i run this I get a Runtime error 70 and Permission Denied message??

Any ideas

Thanks

 

[sulakvea]

the page may be pswd protected?

 

[simonansell]

No the sheet and workbook are unprotected

 

[simonansell]

The following code works to delete all auto shapes, but I only want to delete certain ones based on their colour.


Sub DeleteAllShapes()

Sheet16.Activate

Dim GetShape As Shape

For Each GetShape In ActiveSheet.Shapes
GetShape.Delete
Next

End Sub

 

[sulakvea]

take a look at this
http://support.microsoft.com/kb/180384

P.S. your code is very much like mine, except the "if ... color" part.

did you just copy and paste my code, or u made changes to it?

 

[simonansell]

i am on a standalone pc without any servers.

The code below works and deletes all the auto shapes, with getting a runtime error70. How do i change this code to only delete by colour

Sub DeleteAllShapes()

Sheet16.Activate

Dim GetShape As Shape

For Each GetShape In ActiveSheet.Shapes
GetShape.Delete
Next

End Sub

 

[sulakvea]

replace GetShape.Delete with this:

If GetShape.Fill.ForeColor.SchemeColor = ColorCode Then GetShape.Delete

i just tried your code here and works fine

 

[simonansell]

When i replace the line you have given, I now get the Runtime error 70 again.

If I change back to my original code it works again (All autoshapes get deleted)

Is there an alternative way to delete specific autoshapes based on a certain criteria?