Removing Objects

[Guest]

Hello,

I have a workbook that contains 5 worksheets. In each worksheet, I have a
few picture objects. Is there a way that we can write a code to remove all
the picture objects in the worksheets of the workbook?

Thanks.

 

[Ron de Bruin]

Hi Please Help

Look here
http://www.rondebruin.nl/controlsobjectsworksheet.htm

 

[Bob Phillips]

Ron,

Looking at that link I see that you quote some code from Dave P and myself
regarding deleting shapes (Shapes4). Your example is for Forms controls
only, whereas we have posted code for both Forms and Control Toolbox. I
didn't read the item in depth, it may be you were only looking for Forms
shapes, but just in case, I enclose the full code (PS it does still preserve
data validation and autofilter dropdown arrows <G>)

Sub DeleteShapes()

Dim shp As Shape
Dim testStr As String
Dim OkToDelete As Boolean

For Each shp In ActiveSheet.Shapes
OkToDelete = True

testStr = ""
On Error Resume Next
testStr = shp.TopLeftCell.Address
On Error GoTo 0

If shp.Type = msoFormControl Then
If shp.FormControlType = xlDropDown Then
If testStr = "" Then
'keep it
OkToDelete = False
End If
End If
End If

If OkToDelete Then
shp.Delete
End If

Next shp

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Ron de Bruin]

Hi Bob

The example is only for Forms controls Bob on the page because there is the problem
I maybe add it after I update the page with 2007 stuff

I have send you a private mail Bob

 

[Bob Phillips]

Yeah, thanks I have it. Will reply tonight.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks both. The code works.

Hi Bob

The example is only for Forms controls Bob on the page because there is the problem
I maybe add it after I update the page with 2007 stuff

I have send you a private mail Bob

 

[Guest]

Ron,

Your code and Bob's code work well, except I have a little problem. How can
we modify it to exclude from deleting text boxes? I only want to delete
picture objects, neither forms or control objects.

Thanks.

 

[Ron de Bruin]

Try this


Sub Shapes2()
'Loop through the Shapes collection and use the Type number of the control
Dim myshape As Shape
For Each myshape In ActiveSheet.Shapes

' Pictures
If myshape.Type = 13 Then myshape.Delete

Next myshape
End Sub

 

[Guest]

Ron,

You are the best. Thank you very much. It works.

By the way, where can I go to find out what shape type is what #.

Thanks.

 

[Bob Phillips]

Look for msoShapeType in the object browser, all listed there.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Ron de Bruin]

List is on my site

 

[Guest]

I got it. What is the different between 11 (msoLinkedPicture) and 13
(msoPicture)?

Thanks both.

 

[Bob Phillips]

A picture that is in another app and linked from Excel to that app I would
guess.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)