Excel发现不可读内容 - 数据验证 [英] Excel found unreadable content - Data Validation

问题描述

我有一些我在工作簿上打开的组合框 - 数据源来自数据库。

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

  With Selection.Validation 
 .Delete 
 .Add类型：= xlValidateList ，AlertStyle：= xlValidAlertStop，Operator：= xlBetween，Formula1：= list 
 .IgnoreBlank = False 
 .InCellDropdown = True 
 .ShowInput = True 
 .ShowError = True 
结束
  

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

这一切都很好。当我稍后重新打开工作簿时，会出现问题。我收到一个错误

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

你说是的，Excel然后给你

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

并且从一些组合框中的数据验证已经消失

我怀疑从某些互联网搜索，我用于我的数据验证的字符串太长？ / p>

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

如果是字符串太长可以附加到数据验证，还是有另一个技巧可以用来解决这个问题？

解决方案

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

  With Selection.Validation 
 .Delete 
 .Add类型：= xlValidateList，AlertStyle：= xlValidAlertStop，Operator：= _ 
 xlBetween，Formula1：== listrange
 .IgnoreBlank = True 
 .InCellDropdown = True 
 .ShowInput = True 
 .ShowError = True 
结束
  

你我不会收到该公式字符串太长的错误。

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

动态更新命名空间的大小可能很棘手它们正在被更新的范围内，但是对于VBA来说并不是很难，特别是如果您从数据库中返回集合，那么可以获得记录计数。另一种方法是去ActiveX控制路由，但我喜欢数据验证下拉菜单的干净，本地外观。

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.

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"

You say Yes and Excel then gives you

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

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?

解决方案

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":

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.

这篇关于Excel发现不可读内容 - 数据验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！