Deleting shapes with VB deletes cell validation as well. EH?

[Brettjg]

In an extraordinarily weird week with VB, this is the weirdest of all. I have :
Dim Shp As Shape

and futher on:

Range("title.1:clean.end.personal").ClearComments
'
'validation settings for cells are still there

On Error Resume Next
For Each Shp In ActiveSheet.Shapes
Shp.Delete
Next

''validation settings for cells are gone

I have isolated the code to the area between the two comments (which say
what is happening). How can this possibly be? Earlier in the macro I have run
the identical code in a different sheet and validation settings are
unchanged. EH?
Regards, Brett

 

[Gary''s Student]

Sadly, the Data Validation drop-down is consider to be a Shape. If you
insert Data Validation on two cells, one with only a message-on-click and the
other with a drop-down and then put some other Shape (say something from the
Drawing Toolbar) on the sheet as well and run:

Sub dural()
Dim s As Shape
For Each s In ActiveSheet.Shapes
MsgBox (s.Name)
s.Delete
Next
End Sub

You will see that both the "real" shape and the drop down are called out and
deleted. The Validated cell that only displays a message when clicked will
not be disturbed.

 

[Peter T]

You could include any of these checks before deleting the DV dropdown

If TypeName(sh.DrawingObject) <> "DropDown" Then
If sh.Type <> msoFormControl Then ' any type of Form control
If left$(sh.Name,9) <> "Drop Down" Then

Regards,
Peter T

 

[Ron de Bruin]

See this page
http://www.rondebruin.nl/controlsobjectsworksheet.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

In an extraordinarily weird week with VB, this is the weirdest of all. I have :
Dim Shp As Shape

and futher on:

Range("title.1:clean.end.personal").ClearComments
'
'validation settings for cells are still there

On Error Resume Next
For Each Shp In ActiveSheet.Shapes
Shp.Delete
Next

''validation settings for cells are gone

I have isolated the code to the area between the two comments (which say
what is happening). How can this possibly be? Earlier in the macro I have run
the identical code in a different sheet and validation settings are
unchanged. EH?
Regards, Brett

__________ Information from ESET Smart Security, version of virus signature database 3950 (20090320) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 3952 (20090320) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Brettjg]

Hi GS, well I'll be damned! Peter T has a nice little line that should solve
the problem, but to be frank, it's a ridiculous little bug in Excel. Who
would ever want to delete the dropdowns and retain the validation?

Anyway, on another matter, who the hell is Gary? Regards, Brett

 

[Brettjg]

Thanks Peter, that'll do it. Talk about a trap for the unwary! Regards, Brett

 

[Brettjg]

Thanks Ron, a very vague problem solved. Regards, Brett

 

[Gary''s Student]

Gary taught me Excel and much else.

 

[Brettjg]

Then he taught you well: you've a very great help to me on many occasions,
and my sincere thanks for all of that.

Was Gary into Grateful Dead as well? Any fan of Jerry Garcia has to be at
least a little bit bent. Garcia's head used to spin faster than Linda Blair's.

 

[Jon Peltier]

If you were wary, now, it wouldn't be a trap!

I never thought about this either. I wonder if it hoses the AutoFilter
dropdowns as well.

- Jon

 

[Brettjg]

Hi Jon, yes I think it does. Regards, Brett

If you were wary, now, it wouldn't be a trap!

I never thought about this either. I wonder if it hoses the AutoFilter
dropdowns as well.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______

 

[Peter T]

If you want to delete shapes except DV / filter drop-downs simply use

ActiveSheet.DrawingObjects.Delete

Regards,
Peter T

 

[Brettjg]

Hi Peter, seems this topic has created some interest. Even Gord Dibben didn't
know of this, and that IS saying something. Regards, Brett

 

[Ron de Bruin]

See my page
http://www.rondebruin.nl/controlsobjectsworksheet.htm

This will work for all

Sub Shapes1()
'Delete all Objects except Comments
On Error Resume Next
ActiveSheet.DrawingObjects.Visible = True
ActiveSheet.DrawingObjects.Delete
On Error GoTo 0
End Sub

Or

This example avoid the problem of losing AutoFilter and
Data Validation dropdowns on your worksheet when you use Type 8.

Sub Shapes4()
'Dave Peterson and Bob Phillips
'Example only for the Forms controls
Dim shp As Shape
Dim testStr As String

For Each shp In ActiveSheet.Shapes

If shp.Type = 8 Then
If shp.FormControlType = 2 Then
testStr = ""
On Error Resume Next
testStr = shp.TopLeftCell.Address
On Error GoTo 0
If testStr <> "" Then shp.Delete
Else
shp.Delete
End If
End If

Next shp
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Hi Peter, seems this topic has created some interest. Even Gord Dibben didn't
know of this, and that IS saying something. Regards, Brett



__________ Information from ESET Smart Security, version of virus signature database 3954 (20090323) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 3954 (20090323) __________

The message was checked by ESET Smart Security.

http://www.eset.com