vba Excel 不断加载我的电子表格，但出现错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9008243/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel keep loading my spreadsheet with an error
提问by James
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.
加载电子表格时，我不断收到此错误。它让我修复它，这会剥离所有验证器。该文件保存为 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"
“Excel 在‘’中发现无法读取的内容，您是否希望恢复此工作簿的内容。如果您信任此工作簿的来源，请单击是”
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"
你必须点击是，否则它不会加载。然后我得到这个错误。“Excel 能够通过删除或修复无法读取的内容来打开文件删除功能：来自 /xl/worksheets/sheet1.xml 部分的数据验证”
I haven't a clue about this, and it's really annoying. If anyone has any suggestions, I would be very grateful. Thanks, James
我对此一无所知，这真的很烦人。如果有人有任何建议，我将不胜感激。谢谢，詹姆斯
回答by Doug Glancy
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.
当我在“数据验证”对话框本身中定义了很长的“数据验证”列表时，我遇到了该错误（尽管我现在无法重现它）。如果对话框中有很长的列表，请尝试将列表移动到一个范围内，然后引用该范围。
回答by sharerware
I just had this same issue with my workbook. I found this link the most helpful - https://stackoverflow.com/a/21483680/3653412.
我的工作簿也遇到了同样的问题。我发现这个链接最有帮助 - 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
回答by HARIBABU GL
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
回答by excelSU
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.
这就是我为修复它所做的，我在工作簿中另一张工作表的一列中填写了我想要的列表，然后引用了该工作表。我制作了工作表名称，我从 SheetName 引用列表（只需在此处输入您的工作表名称），而 A2:A19 正在引用参考表的那些单元格。如果需要，这也可以更轻松地编辑列表。
Sub Test()
Worksheets("Sheet1").Activate
With rng.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=SheetName!$A:$A" 'Replace SheetName and the range with yours
End With
End Sub 