Deleting ActiveSheet.Shapes removes validation dropdowns

[MrExcel]

I ran into a bizarre worksheet problem yesterday. We've figured out the "why" and so I am posting this to document one problem, but I am also looking for solutions to figure out the "what to do about it".

1. I found a workbook with many worksheets.
One 1 worksheet there were two cells with validation that were set to display the in-cell dropdown. Neither would display the in cell dropdown.

2. If I added a new validation to this sheet, it would not display the dropdown.

3. If I added a new validation to another sheet, the dropdown displayed and it worked perfectly.

4. In the problem sheet, the validation rules do work. If I try to enter something not in the list, I do get the error box, so the logic for the validation is working.

This problem has been discussed many times on this board. As many have posted, validation above the freeze panes row would not work in Excel 97. Sometimes the validation list range gets corrupted. None of this was the issue on this worksheet.

I now know how it happens. You can easily duplicate this yourself:
A. Open a new workbook with 2 worksheets
B. Add validation to a cell on each worksheet. Specify a list, in-cell dropdown.
C. With sheet1 active, go to the VBA immediate window. Run this code:
For Each obj in ActiveSheet.Shapesbj.Delete:Next
D. None of the validation dropdowns on Sheet1 work anymore. All future dropdowns will not work on that sheet.

When I started writing VBA, I would use that line of code a lot. It is a fairly evil line of code. If you insert some cell comments on a worksheet and run that code, it will permanently corrupt the worksheet. As soon as you get near the red triangle in the commented cell, Excel will GPF and crash. I quit using that logic to avoid the cell comment problem, but it obviously causes the dropdown problem as well.

Long build-up to the question: Is there any way to repair the sheet once this has been done?

Our not-so-elegant workaround was to insert a blank worksheet, cut all the cells from the damaged sheet and paste them to the new sheet. This isn't perfect, because you lose column widths and row heights.

Any thoughts would be appreciated.

Bill

 

[Andrew Poulsom]

Our not-so-elegant workaround was to insert a blank worksheet, cut all the cells from the damaged sheet and paste them to the new sheet. This isn't perfect, because you lose column widths and row heights.

Bill,

Copying and pasting all the cells worked for me (Excel 2000). The column widths and row heights were also replicated.

 

[MrExcel]

Andrew - Copy & Paste to a new worksheet would work - we use cut because 10 other worksheets were pointing to cells on the damaged worksheet.

I tried Copy & Paste back to the damaged worksheet, but that did not work in Excel 2002.

 

[Andrew Poulsom]

Bill,

Copy/Paste then Cut/Paste replicates the column widths/row heights and preserves the links.

 

[MrExcel]

For completeness, here is what we've found on this issue:

1) When we add comment or validation (in-cell dropdown), Excel will add a control to the sheet. Excel will operate the control to be visible or invisible.

2) For comment, Excel will add one text box per comment.
For validation, Excel will add one dropdown for all validation.
Thus, if there were 5 comments and 5 validations, we will have 6 controls - 5 text boxes, and 1 drop down.

3) Comment text box will be named as "Comment x", and Validation dropdown will be named as "Drop Down x". And x will be a global sequence number.

For example follow these steps, we will get:

Step                   Control Name
Add Comment     Comment 1
Add Validation     Drop Down 2
Add Comment     Comment 3
Add Validation     Nothing happened
Add Comment     Comment 4
Add Validation     Nothing happened 
Add Comment     Comment 5
Add Validation     Nothing happened 
Add Comment     Comment 6
Add Validation     Nothing happened

4) If we add a validation, and then remove it, the drop down will not be removed.

5) If we add a comment, and then remove it, the text box will be removed. But the global sequence number will not be rolled back.

So, we could change the code to ...

For Each obj in ActiveSheet.Shapes
    Select Case TypeName(obj.DrawingObject)
        Case "DropDown"	
            If Left(obj.Name, 10) <> "Drop Down " Then obj.Delete
        Case "TextBox"
            If Left(obj.Name, 8) <> "Comment " Then obj.Delete
        Case Else
            Obj.Delete
    End Select
Next obj

There doesn't seem to be anything that you can do to correct the problem once the Drop Down control has been deleted, but using the above code instead of the original code would prevent the problem from happening in the first place.

 

[parry]

Hi, I have solved the riddle - do I get a chocolate fish?
You can use Open and Repair to fix the validation error.

1. Close the book if open then select File|Open and navigate to the workbook but do not open yet. The Open button has a drop-down box - select this and choose Open & Repair.

2. Excel will open and a dialogue box will appear with details "Lost Data Validation information in one or more sheets." and creates a log file.

3. You need to Save the workbook - Excel automatically uses Save As because it needs to be saved as a new workbook.

regards
parry.

 

[SPDavern]

Open & Repair

I don't seem to have an "Open & Repair" option from the Open File dialog box open button. I'm using Excel 2000. Is this feature only available in later versions or is there a way to enable that option?
Thanks,
Sean

 

[ncavallo]

Great Work! This was definitely a tough one. I am using Excel 2003, and you just saved me from having to redo hours of work on a version of my spreadsheet before I deleted the autoshape.

 

[drusbury]

I would like to add that Open & Repair in XL2007 did not work for me, so I tried the Open and Repair in XL2003, which worked & restored my Dropdown controls.

This is why I must keep XL2003, XL2007, XL2010 on my machine