vba Excel 发现无法读取的内容 - 数据验证

Excel found unreadable content - Data Validation

提问by David

I have some combo boxes that I populate on opening of the workbook - the source of the data comes from a database.

我在打开工作簿时填充了一些组合框 - 数据源来自数据库。

I populate my combo boxes using data validation with the following code:-

我使用以下代码使用数据验证填充我的组合框：-

  With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=list
    .IgnoreBlank = False
    .InCellDropdown = True
    .ShowInput = True
    .ShowError = True
  End With

where list is a comma separated string that I have built up from the database recordset.

其中 list 是我从数据库记录集构建的逗号分隔字符串。

This all works fine. The problem arises when I re-open the workbook later on. I get an error

这一切正常。稍后我重新打开工作簿时出现问题。我收到一个错误

"Excel found unreadable content. Do you want to recover the contents of this file"

“Excel 发现无法读取的内容。是否要恢复此文件的内容”

You say Yes and Excel then gives you

你说是，Excel 然后给你

"Excel was able to repair the file by removing features"

“Excel 能够通过删除功能修复文件”

And the data Validation from some of the Combo boxes is gone

来自某些组合框的数据验证消失了

I suspect from some internet searching that the string I'm using for my Data Validation is too long?

我从一些互联网搜索中怀疑我用于数据验证的字符串太长？

It isn't an option for me to add the recordset values to a hidden sheet and set the Data Validation source to a range on the hidden sheet as the combo boxes are dynamic and chop and change depending on user selection. I really just need to be able to set the Data Validation to my string that I have built up at various points in the user interaction.

将记录集值添加到隐藏工作表并将数据验证源设置为隐藏工作表上的范围对我来说不是一个选项，因为组合框是动态的，并且会根据用户选择进行切割和更改。我真的只需要能够将数据验证设置为我在用户交互中的各个点建立的字符串。

If it is a case of the string being too long is it possible to append to Data Validation or is there another trick I can use to get around this issue?

如果是字符串太长的情况，是否可以附加到数据验证，或者我可以使用其他技巧来解决这个问题吗？

回答by Baodad

I've manipulated validation lists before in some of my Excel projects. When you set validation to Allow:List, you can set your data Source to be a workbook-level named range. In this example, I've defined a named range "listrange":

我之前在我的一些 Excel 项目中操作过验证列表。当您将验证设置为 Allow:List 时，您可以将数据源设置为工作簿级别的命名范围。在这个例子中，我定义了一个命名范围“listrange”：

With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=listrange"
    .IgnoreBlank = True
    .InCellDropdown = True
    .ShowInput = True
    .ShowError = True
End With

You'll never get an error for that formula string being too long.

您永远不会因为公式字符串太长而出错。

I put all my validation-referenced named ranges in one worksheet, and make it hidden. Then my code manipulates those named ranges, which in turn update the values available from the validation drop-down menus.

我将所有验证引用的命名范围放在一张工作表中，并将其隐藏。然后我的代码操作这些命名范围，这反过来更新验证下拉菜单中可用的值。

It can be tricky to dynamically update the size of the named ranges while they are being updated, but it's not too hard with VBA, particularly not if you're returning sets from a database, where you can get a record count. The alternative is to go the ActiveX control route, but I like the clean, native look and feel of the data validation drop-downs.

在更新命名范围的同时动态更新它们的大小可能会很棘手，但使用 VBA 并不难，尤其是当您从数据库返回集合时，您可以获得记录计数。另一种方法是采用 ActiveX 控件路线，但我喜欢数据验证下拉菜单的干净、原生的外观和感觉。

回答by Wolfgang Kuehn

Just ran into this issue (limit on data validation formula length at workbook opening), and like the OP wouldn't want to go with auxiliary ranges.

刚刚遇到这个问题（工作簿打开时数据验证公式长度的限制），就像 OP 不想使用辅助范围一样。

My workaround is to delete the validations in the Workbook_BeforeSavehandler.

我的解决方法是删除Workbook_BeforeSave处理程序中的验证。

My use case is to always refresh the data from external sources, so it is a viable option to always delete all imported data and validations before saving the workbook.

我的用例是始终刷新来自外部源的数据，因此在保存工作簿之前始终删除所有导入的数据和验证是一个可行的选择。

回答by DataPispor

I've solved this issue by deleting my validation in WorkbookBeforeSave event. However, I'm using C#

我通过删除 WorkbookBeforeSave 事件中的验证解决了这个问题。但是，我正在使用 C#

回答by Kazimierz Jawor

It seems that you are right with the string length of Validation formula1 parameter. My suggestion for you is as follows (additional information are placed as comments within the code):

似乎您对 . 的字符串长度是正确的Validation formula1 parameter。我给你的建议如下（附加信息放在代码中作为注释）：

'Split your list into array, or if data are Array before you _
create List variable you could combine some of earlier steps _
of your code

    List = Split(List, ",")
'paste your list into hidden sheet as of A1 direction bottom, _
we need to transpose our Array to do so
    Sheets("hidden").Range("a1").Resize(UBound(List) + 1, 1) = Application.Transpose(List)

 With Selection.Validation
    .Delete
    'here we need to change definition of formula
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
    Formula1:="=Hidden!A1:A" & UBound(List) + 1
    .IgnoreBlank = False
    .InCellDropdown = True
    .ShowInput = True
    .ShowError = True
  End With

回答by Angel

there is a way around, save the strings you use for your conditional formatting somewhere in the workbook before using it. when you use them, reference them to the range where you saved them and not from the strings. Remember longs string in conditional formatting is what causes it do a function that when closing the workbook wipes out the problematic condition formatting and another function that when opening puts them back on

有一种方法可以在使用之前将用于条件格式的字符串保存在工作簿中的某处。当您使用它们时，将它们引用到您保存它们的范围，而不是从字符串中引用。请记住，条件格式中的 longs 字符串是导致它执行一个功能的原因，该功能在关闭工作簿时会清除有问题的条件格式，而另一个功能是在打开时将它们重新打开

Problem solved and it works a treat :)

问题解决了，效果很好:)