Excel keep loading my spreadsheet with an error

Question

I keep getting this error when I load my spreadsheet. It makes me repair it, which strips all the validators. The file is saved as an xlsm.

"Excel found unreadable content in '' Do you wnat to recover the contents of this workbook. If you trust the source of this workbook, click yes"

You have to click yes or it won't load. Then I get this error. "Excel was able to open the file by removing or repairing the unreadable content Removed Feature: Data validation from /xl/worksheets/sheet1.xml part"

I haven't a clue about this, and it's really annoying. If anyone has any suggestions, I would be very grateful. Thanks, James

Answer 1

I've gotten that error when I had a long Data Validation list defined in the Data Validation dialog itself (although I can't reproduce it now). If you have a long list in the dialog, try moving the list to a range and then referring to the range.

Answer 2

I just had this same issue with my workbook. I found this link the most helpful - https://stackoverflow.com/a/21483680/3653412.

While the accepted answer would have ultimately addressed the issue for me (rebuilding the workbook), it would have taken a significant amount of time. Clearing the sortfields ultimately fixed the issue for me.

Sub clearSortFields()
Dim ws as worksheet
ThisWorkbook.Activate
For Each ws In Worksheets
    ws.Sort.SortFields.Clear
Next ws
End Sub

Answer 3

By removing Validation Cells and once again running the code for revalidating on open of workbook solves this issue

Sub RemValidation()
Dim ARows As Variant
Dim i As Double
ARows = Split("C3,C4,C5,C6,C14,C19,C20,C25,F4", ",")

For i = 0 To UBound(ARows)
ThisWorkbook.Sheets("WO").Range(ARows(i)).Validation.Delete
Next

End Sub

Answer 4

This is what I did to fix it, I filled in what I wanted in the list down a column of another sheet in the workbook and then referenced that sheet. I made the sheet name I am reference the list from SheetName (just put your sheet name here) and A2:A19 is referenceing those cells of the reference sheet. This also makes it easier to edit the list if you need to.

Sub Test()
Worksheets("Sheet1").Activate
With rng.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=SheetName!$A$2:$A$19" 'Replace SheetName and the range with yours
End With
End Sub 

Answer 5

The issue is out of date, but there may still be those looking for a solution to this problem. When I opened the workbook containing the data validation lists that I created using VBA codes, I was encountering the same problem. On this, I added code snippet to delete data validation lists while closing the workbook :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
   For Each ws In ThisWorkbook.Worksheets
      On Error Resume Next
      ws.Cells.SpecialCells(xlCellTypeAllValidation).Validation.Delete
      On Error GoTo 0
   Next ws
End Sub

As shown in the picture, I have created data validation lists with ordered and unique values using VBA codes and added them to cells in column A.

Source file