Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation Lost After Clearing All Shapes
I'm trying to delete all shapes on a sheet which works using the below code,
but a problem I'm having is that any validation list on the same sheet is also deleted, and once delete validation lists will NOT appear on that same sheet again even after restarting excel. Does anyone know how to either clear all shapes but NOT validation lists, or have any knowledge about why this occurs and why you then can't add a new validation list (If I goto data validation the list settings are correct and reference the list named range - doing the same on another tab works correctly). For Each oShape In Sheets("Sheet1").Shapes oShape.Delete Next oShape |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation Lost After Clearing All Shapes
You could try using the drawingobjects collection instead.
It doesn't *seem* to include the validation drop-downs. For Each drw In Sheets("Sheet1").DrawingObjects .... Next drw Tim "Shawn" wrote in message ... I'm trying to delete all shapes on a sheet which works using the below code, but a problem I'm having is that any validation list on the same sheet is also deleted, and once delete validation lists will NOT appear on that same sheet again even after restarting excel. Does anyone know how to either clear all shapes but NOT validation lists, or have any knowledge about why this occurs and why you then can't add a new validation list (If I goto data validation the list settings are correct and reference the list named range - doing the same on another tab works correctly). For Each oShape In Sheets("Sheet1").Shapes oShape.Delete Next oShape |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation Lost After Clearing All Shapes
Good suggestion, but it ended up giving me an error (Delete method of picture
class failed - possible it's not .Delete). I figured out that the following works. Strange part is that I still can't enter validation lists on the sheet I used the other code on unless I create a new sheet or workbook. For Each oShape In Sheets("Sheet1").Shapes If oShape.Type = msoPicture Then oShape.Delete Next oShape "Tim Williams" wrote: You could try using the drawingobjects collection instead. It doesn't *seem* to include the validation drop-downs. For Each drw In Sheets("Sheet1").DrawingObjects .... Next drw Tim "Shawn" wrote in message ... I'm trying to delete all shapes on a sheet which works using the below code, but a problem I'm having is that any validation list on the same sheet is also deleted, and once delete validation lists will NOT appear on that same sheet again even after restarting excel. Does anyone know how to either clear all shapes but NOT validation lists, or have any knowledge about why this occurs and why you then can't add a new validation list (If I goto data validation the list settings are correct and reference the list named range - doing the same on another tab works correctly). For Each oShape In Sheets("Sheet1").Shapes oShape.Delete Next oShape |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation Lost After Clearing All Shapes
How about:
Sub test() Dim oshape As Shape Dim testIt As Boolean ' For Each oshape In Sheets("Sheet1").Shapes On Error Resume Next ' Non drop-downs cause an error two lines down! testIt = False testIt = oshape.FormControlType = xlDropDown If (Not testIt) Then oshape.Delete Next oshape End Sub HTH, Eric "Shawn" wrote: I'm trying to delete all shapes on a sheet which works using the below code, but a problem I'm having is that any validation list on the same sheet is also deleted, and once delete validation lists will NOT appear on that same sheet again even after restarting excel. Does anyone know how to either clear all shapes but NOT validation lists, or have any knowledge about why this occurs and why you then can't add a new validation list (If I goto data validation the list settings are correct and reference the list named range - doing the same on another tab works correctly). For Each oShape In Sheets("Sheet1").Shapes oShape.Delete Next oShape |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting shapes with VB deletes cell validation as well. EH? | Excel Programming | |||
Used drawing colors in shapes....lost default colors for "Fill Col | Excel Discussion (Misc queries) | |||
I have lost the in cell drop down in validation | Excel Worksheet Functions | |||
Validation lost on advanced filter copy | Excel Programming | |||
Excel VBA - Clearing Shapes and Cell Contents | Excel Programming |