| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting shapes with VB deletes cell validation as well. EH?
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting shapes with VB deletes cell validation as well. EH?
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. -- Gary''s Student - gsnu200840 "Brettjg" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting shapes with VB deletes cell validation as well. EH?
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 "Brettjg" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting shapes with VB deletes cell validation as well. EH?
See this page
http://www.rondebruin.nl/controlsobjectsworksheet.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brettjg" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting shapes with VB deletes cell validation as well. EH?
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 "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200840 "Brettjg" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting shapes with VB deletes cell validation as well. EH?
Thanks Peter, that'll do it. Talk about a trap for the unwary! Regards, Brett
"Peter T" wrote: 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 "Brettjg" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting shapes with VB deletes cell validation as well. EH?
Thanks Ron, a very vague problem solved. Regards, Brett
"Ron de Bruin" wrote: See this page http://www.rondebruin.nl/controlsobjectsworksheet.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Brettjg" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting shapes with VB deletes cell validation as well. EH?
Gary taught me Excel and much else.
-- Gary''s Student - gsnu200840 "Brettjg" wrote: 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 "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200840 "Brettjg" wrote: 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting shapes with VB deletes cell validation as well. EH?
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. "Gary''s Student" wrote: Gary taught me Excel and much else. -- Gary''s Student - gsnu200840 "Brettjg" wrote: 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 "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200840 "Brettjg" wrote: 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting shapes with VB deletes cell validation as well. EH?
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/ _______ "Brettjg" wrote in message ... Thanks Peter, that'll do it. Talk about a trap for the unwary! Regards, Brett "Peter T" wrote: 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 "Brettjg" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting shapes with VB deletes cell validation as well. EH?
Hi Jon, yes I think it does. Regards, Brett
"Jon Peltier" wrote: 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/ _______ "Brettjg" wrote in message ... Thanks Peter, that'll do it. Talk about a trap for the unwary! Regards, Brett "Peter T" wrote: 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 "Brettjg" wrote in message ... 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting shapes with VB deletes cell validation as well. EH?
If you want to delete shapes except DV / filter drop-downs simply use
ActiveSheet.DrawingObjects.Delete Regards, Peter T "Brettjg" wrote in message ... Hi Jon, yes I think it does. Regards, Brett "Jon Peltier" wrote: 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/ _______ "Brettjg" wrote in message ... Thanks Peter, that'll do it. Talk about a trap for the unwary! Regards, Brett "Peter T" wrote: 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 "Brettjg" wrote in message ... 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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting shapes with VB deletes cell validation as well. EH?
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 "Brettjg" wrote in message ... Hi Peter, seems this topic has created some interest. Even Gord Dibben didn't know of this, and that IS saying something. Regards, Brett "Peter T" wrote: If you want to delete shapes except DV / filter drop-downs simply use ActiveSheet.DrawingObjects.Delete Regards, Peter T "Brettjg" wrote in message ... Hi Jon, yes I think it does. Regards, Brett "Jon Peltier" wrote: 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/ _______ "Brettjg" wrote in message ... Thanks Peter, that'll do it. Talk about a trap for the unwary! Regards, Brett "Peter T" wrote: 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 "Brettjg" wrote in message ... 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 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Deleting Rows - only deletes content | Excel Discussion (Misc queries) | |||
| Deleting Shapes | Excel Discussion (Misc queries) | |||
| deleting all shapes and lines | Excel Programming | |||
| Deleting shapes | Excel Programming | |||
| Deleting shapes | Excel Programming | |||
Linear Mode
