ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Removing Objects (https://www.excelbanter.com/excel-programming/375508-removing-objects.html)

Please Help

Removing Objects
 
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

Removing Objects
 
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.




Bob Phillips

Removing Objects
 
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 de Bruin

Removing Objects
 

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.








Bob Phillips

Removing Objects
 
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.









Please help

Removing Objects
 
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.








Please help

Removing Objects
 
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 de Bruin

Removing Objects
 
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.







Please help

Removing Objects
 
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.







Bob Phillips

Removing Objects
 
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.








Ron de Bruin

Removing Objects
 
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.









Please help

Removing Objects
 
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.










All times are GMT +1. The time now is 09:41 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com