CODEWITHSUNDEEP
excelvba
David
David

Reputation: 1293

Excel found unreadable content - Data Validation

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?

Upvotes: 6

Views: 13235

Answers (5)

DataPispor
DataPispor

Reputation: 381

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

Upvotes: 2

Angel
Angel

Reputation: 1

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

Problem solved and it works a treat :)

Upvotes: 0

Wolfgang Kuehn
Wolfgang Kuehn

Reputation: 12926

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.

My workaround is to delete the validations in the Workbook_BeforeSave handler.

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.

Upvotes: 2

Baodad
Baodad

Reputation: 2501

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.

Upvotes: 5

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

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

'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

Upvotes: 1

Related Questions