Dear experts,
I was trying to write a macro to select all shapes in an active sheet except
2 macro buttons.
I have been looking on the treads and used the below code. It works, only
after I have run it the list that I had in one of the cells on my sheet
(data--validation--list) is not showing any longer!

Can you please help me? I am using Excel 2003.
Many thanks in advance,
Best regards

Valeria

Dim Shp As Object
Dim InitialShape As Boolean

InitialShape = True

For Each Shp In ActiveSheet.Shapes
If Shp.Name = "Button 259" Then
'skip it
ElseIf Shp.Name = "Button 254" Then
'skip it
Else
Shp.Select Replace:=InitialShape
InitialShape = False
Shp.Delete
End If

Next Shp

The bad news is that there are lots of shapes that you may want to keep
(comments, autofilter arrows, datavalidation arrows).

Ron de Bruin has lots of tips he
http://www.rondebruin.nl/controlsobjectsworksheet.htm

So maybe...

Option Explicit
Sub shapes2()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
On Error Resume Next
If shp.TopLeftCell Is Nothing Then
'skip it
Else
Select Case LCase(shp.Name)
Case Is = LCase("Button 259"), LCase("Button 254")
'skip it
Case Else
shp.Delete
End Select
End If
On Error GoTo 0
Next shp
End Sub

But test this against a copy of the worksheet--or save before you run it, so you
can close without saving if it blows up!

If it doesn't work correctly, maybe you could post more information about the
shapes you want to delete--where they came from (Forms toolbar, control toolbox
toolbar, drawing toolbar) and what they are.




Valeria wrote:

Dear experts,
I was trying to write a macro to select all shapes in an active sheet except
2 macro buttons.
I have been looking on the treads and used the below code. It works, only
after I have run it the list that I had in one of the cells on my sheet
(data--validation--list) is not showing any longer!

Can you please help me? I am using Excel 2003.
Many thanks in advance,
Best regards

Valeria

Dim Shp As Object
Dim InitialShape As Boolean

InitialShape = True

For Each Shp In ActiveSheet.Shapes
If Shp.Name = "Button 259" Then
'skip it
ElseIf Shp.Name = "Button 254" Then
'skip it
Else
Shp.Select Replace:=InitialShape
InitialShape = False
Shp.Delete
End If

Next Shp

--

Dave Peterson

Hello Dave,
I have tested your code and it works perfectly, thank you! In fact, the only
thing I want to delete are images (pasted into Excel) and groups of images,
however aside from the 2 macro buttons I have no other shapes in the
worksheet.
Again thanks,
Kind regards,
--
Valeria


"Dave Peterson" wrote:

The bad news is that there are lots of shapes that you may want to keep
(comments, autofilter arrows, datavalidation arrows).

Ron de Bruin has lots of tips he
http://www.rondebruin.nl/controlsobjectsworksheet.htm

So maybe...

Option Explicit
Sub shapes2()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
On Error Resume Next
If shp.TopLeftCell Is Nothing Then
'skip it
Else
Select Case LCase(shp.Name)
Case Is = LCase("Button 259"), LCase("Button 254")
'skip it
Case Else
shp.Delete
End Select
End If
On Error GoTo 0
Next shp
End Sub

But test this against a copy of the worksheet--or save before you run it, so you
can close without saving if it blows up!

If it doesn't work correctly, maybe you could post more information about the
shapes you want to delete--where they came from (Forms toolbar, control toolbox
toolbar, drawing toolbar) and what they are.




Valeria wrote:

Dear experts,
I was trying to write a macro to select all shapes in an active sheet except
2 macro buttons.
I have been looking on the treads and used the below code. It works, only
after I have run it the list that I had in one of the cells on my sheet
(data--validation--list) is not showing any longer!

Can you please help me? I am using Excel 2003.
Many thanks in advance,
Best regards

Valeria

Dim Shp As Object
Dim InitialShape As Boolean

InitialShape = True

For Each Shp In ActiveSheet.Shapes
If Shp.Name = "Button 259" Then
'skip it
ElseIf Shp.Name = "Button 254" Then
'skip it
Else
Shp.Select Replace:=InitialShape
InitialShape = False
Shp.Delete
End If

Next Shp

--

Dave Peterson

Be careful with your language <bg.

You had other shapes--the data|validation dropdown arrow is a shape that you
want to keep.



Valeria wrote:

Hello Dave,
I have tested your code and it works perfectly, thank you! In fact, the only
thing I want to delete are images (pasted into Excel) and groups of images,
however aside from the 2 macro buttons I have no other shapes in the
worksheet.
Again thanks,
Kind regards,
--
Valeria

"Dave Peterson" wrote:

The bad news is that there are lots of shapes that you may want to keep
(comments, autofilter arrows, datavalidation arrows).

Ron de Bruin has lots of tips he
http://www.rondebruin.nl/controlsobjectsworksheet.htm

So maybe...

Option Explicit
Sub shapes2()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
On Error Resume Next
If shp.TopLeftCell Is Nothing Then
'skip it
Else
Select Case LCase(shp.Name)
Case Is = LCase("Button 259"), LCase("Button 254")
'skip it
Case Else
shp.Delete
End Select
End If
On Error GoTo 0
Next shp
End Sub

But test this against a copy of the worksheet--or save before you run it, so you
can close without saving if it blows up!

If it doesn't work correctly, maybe you could post more information about the
shapes you want to delete--where they came from (Forms toolbar, control toolbox
toolbar, drawing toolbar) and what they are.




Valeria wrote:

Dear experts,
I was trying to write a macro to select all shapes in an active sheet except
2 macro buttons.
I have been looking on the treads and used the below code. It works, only
after I have run it the list that I had in one of the cells on my sheet
(data--validation--list) is not showing any longer!

Can you please help me? I am using Excel 2003.
Many thanks in advance,
Best regards

Valeria

Dim Shp As Object
Dim InitialShape As Boolean

InitialShape = True

For Each Shp In ActiveSheet.Shapes
If Shp.Name = "Button 259" Then
'skip it
ElseIf Shp.Name = "Button 254" Then
'skip it
Else
Shp.Select Replace:=InitialShape
InitialShape = False
Shp.Delete
End If

Next Shp

--

Dave Peterson


--

Dave Peterson