Excel Found Unreadable Content - Data Validation

Question

I have some combined fields that I fill out when I open the book — the data source comes from the database.

I fill out my combo box by checking the data 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 created from a set of database records.

All of this works great. The problem arises when I open the book again. I get an error

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

You say yes and excel then give you

"Excel was able to recover the file by deleting the functions"

And data verification from some Combo fields disappeared

I suspect that in some internet searches the string that I use for my data verification is too long?

I do not need to add recordset values ​​to the hidden sheet and set the source of the data check for the range on the hidden sheet, since the combo boxes are dynamic and intermittent and change depending on the user's choice. I really need to be able to configure Data Validation to my string, which I created at various points of user interaction.

If this line is too long, can I add to the data validation or is there another trick I can use to get around this problem?

Answer 1

I worked on checklists earlier in some of my Excel projects. When you set the check to "Allow: List", you can set the data source as a range of names at the workbook level. In this example, I defined the 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 will never get an error for a formula line that is too long.

I put all of my named ranges specified in the validation on one worksheet and make it hidden. My code then manages these named ranges, which in turn update the values ​​available from the validation drop-down menus.

It may be difficult to dynamically update the size of named ranges while they are being updated, but it is not too complicated with VBA, especially if you are returning sets from a database where you can get a record counter. An alternative is to move along the ActiveX control path, but I like the clean, natural look and feel of the data palettes.

Answer 2

I solved this problem by removing my validation in the WorkbookBeforeSave event. However, I use C #

Answer 3

It seems that you are correct with the string length of the Validation formula1 parameter . My suggestion for you is the following (additional information is placed in the form of comments in the code):

 '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 

Answer 4

I just ran into this problem (limiting the length of the data validation formula when opening the book), and, like the OP does not want to use auxiliary ranges.

My workaround is to remove the checks in the Workbook_BeforeSave handler.

My use case is to always update data from external sources, so to save a workbook, you can always delete all imported data and checks.

Answer 5

there is a way, save the lines that you use for conditional formatting somewhere in the workbook before using it. when you use them, refer to the range in which you saved them, and not from the lines. Remember, a long line in conditional formatting is what causes it to execute a function that, when closing a book, erases the formatting of problematic conditions and another function that, when opened, returns them to

The problem is resolved and it works :)