PDA

View Full Version : [SOLVED] Deleting all images in a worksheet



jungix
07-24-2006, 06:21 AM
Hi,

I know how to delete one image that I see in a worksheet, but how can I manage to delete all images on a worksheet, knowing that I don't know how many there are and what their number are?

Jungix

Bob Phillips
07-24-2006, 07:17 AM
You need to be careful


Sub DeleteShapes()
Dim oShape As Shape
Dim sTest As String
Dim OKToDelete As Boolean
For Each oShape In ActiveSheet.Shapes
OKToDelete = True
sTest = ""
On Error Resume Next
sTest = oShape.TopLeftCell.Address
On Error GoTo 0
If oShape.Type = msoFormControl Then
If oShape.FormControlType = xlDropDown Then
If sTest = "" Then
'keep it
OKToDelete = False
End If
End If
End If
If OKToDelete Then
oShape.Delete
End If
Next shp
End Sub

jungix
07-24-2006, 08:05 AM
Thanks, with Next oShape it worked on my sample.

Why did you say I should be careful? What is your sTest doing?

Bob Phillips
07-24-2006, 09:37 AM
Thanks, with Next oShape it worked on my sample.

Why did you say I should be careful? What is your sTest doing?

Because the easy way is to go



For Each oShape In ActiveSheet.Shapes
oShape.Delete
Next oShape


but the problem here is that it also removes the Data Validation dropdown and the Autofilter dropdown, and there is no way to get them back. I developed the convoluted method shown previously to avoid this.

matthewspatrick
07-25-2006, 04:52 PM
but the problem here is that it also removes the Data Validation dropdown and the Autofilter dropdown, and there is no way to get them back.

:omg:

That's a pretty significant issue!

Bob Phillips
07-26-2006, 12:18 AM
:omg:

That's a pretty significant issue!
Slight correction, you can re-create the autofilter which will restore that arrow, that's a pain but it can be done, but I know of no way to get the DV arrow back. The even worse part of that is that it is gone from the sheet, thaht is if you create a new List DV on that sheet, it too will not have an arrow.