CODEWITHSUNDEEP
excelvba
James
James

Reputation: 1852

Excel keep loading my spreadsheet with an error

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

Upvotes: 11

Views: 24614

Answers (7)

Rahul
Rahul

Reputation: 29

I got the same error, and I realised my mistake by reading this article, in relevant part:

Due to how Data validations are implemented in Excel/Office Open Xml it is always better to use a shared range rather than adding the validations on each row. For example: if you have 50 000 cells and wants to add a data validation on each one of them try - if feasible - to define a common validation for the cells (i.e. Worksheet.AddListValidation("C1:C50000")) rather than setting them per row.

screenshot of the above-quoted text

Upvotes: 0

TamilRascal
TamilRascal

Reputation: 1

The easiest way to fix this seems to be simply deleting the validation before exiting the workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wkbk As Workbook
Dim wksh1 As Worksheet
Set wkbk = ActiveWorkbook
Set wksh1 = wkbk.Sheets("RTNL Gen")
'Step 1:  Activate the message box and start the check
    Select Case MsgBox("Save and Close?", vbOKCancel, "Rationale Generator"
'Step 2: Cancel button pressed, cancel the close
    Case Is = vbCancel
    Cancel = True
'Step 3: OK button pressed, DELETE VALIDATION, save the workbook and close
    Case Is = vbOK
    wksh1.Range("A2").Validation.Delete
    wkbk.Save
'Step 4: Close your Select Case statement
    End Select
End Sub

Upvotes: 0

kadrleyn
kadrleyn

Reputation: 384

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

enter image description here

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

Upvotes: 1

HARIBABU GL
HARIBABU GL

Reputation: 11

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

Upvotes: 1

excelSU
excelSU

Reputation: 33

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

Upvotes: 0

sharerware
sharerware

Reputation: 51

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

Upvotes: 5

Doug Glancy
Doug Glancy

Reputation: 27478

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.

Upvotes: 11

Related Questions