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.

Hi Please Help

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Please Help" <Please wrote in message ...
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,

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" wrote in message
...
Hi Please Help

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Please Help" <Please wrote in message
...
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.

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Bob Phillips" wrote in message ...
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" wrote in message
...
Hi Please Help

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Please Help" <Please wrote in message
...
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.

Yeah, thanks I have it. Will reply tonight.

--
HTH

Bob Phillips

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

"Ron de Bruin" wrote in message
...

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Bob Phillips" wrote in message
...
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" wrote in message
...
Hi Please Help

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Please Help" <Please wrote in message
...
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.

Thanks both. The code works.

"Ron de Bruin" wrote:


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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Bob Phillips" wrote in message ...
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" wrote in message
...
Hi Please Help

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Please Help" <Please wrote in message
...
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,

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" wrote:

Hi Please Help

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Please Help" <Please wrote in message ...
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.

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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Please Help" wrote in message ...
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" wrote:

Hi Please Help

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Please Help" <Please wrote in message ...
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,

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.

"Ron de Bruin" wrote:

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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Please Help" wrote in message ...
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" wrote:

Hi Please Help

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Please Help" <Please wrote in message ...
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.

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" wrote in message
...
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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Please Help" wrote in message
...
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" wrote:

Hi Please Help

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Please Help" <Please wrote in message
...
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.

List is on my site

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Please Help" wrote in message ...
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.

"Ron de Bruin" wrote:

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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Please Help" wrote in message ...
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" wrote:

Hi Please Help

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Please Help" <Please wrote in message
...
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.

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

Thanks both.

"Bob Phillips" wrote:

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" wrote in message
...
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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Please Help" wrote in message
...
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" wrote:

Hi Please Help

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Please Help" <Please wrote in message
...
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.