Data Validation

[tiredofit]

I have a workbook where I have added some code so that a cell can only take certain values, via Data Validation.

The list exceeds 255 characters, ie the list of available values are:

"a,b,c,d,e, etc. "

When I save, close then reopen the workbook, I get this error message:

[code]

We found a problem with some content in 'Your workbook.xlsm.' Do you want to try to recover as much as we can? If you trust the source of this workbook, click Yes.

then it shows:

Removed feature: Data Validation from /xl/worksheets/sheet1.xml part

Has this got anything to do with the Data Validation being too long?

Thanks

 

[abbeyWigan]

No it has nothing to do with the data being too long. I think you need to exceed 32768 characters (if my memory serves me right) before you run into a different problem to the one you have reported.

What it most likely has to do with is that, when you open your file, Excel could not locate where those validation data are.

How are you setting the validation data? Post the code and I'll have look at it for you.

 

[tiredofit]

No it has nothing to do with the data being too long. I think you need to exceed 32768 characters (if my memory serves me right) before you run into a different problem to the one you have reported.

What it most likely has to do with is that, when you open your file, Excel could not locate where those validation data are.

How are you setting the validation data? Post the code and I'll have look at it for you.

Thanks, here's the code.

Sub Start()

Dim NumberOfWorksheets As Integer
    
    NumberOfWorksheets = ThisWorkbook.Worksheets.Count
    
    Dim WorksheetsArray() As Variant
    
    ReDim WorksheetsArray(1 To NumberOfWorksheets - 1) As Variant
    
    Dim i As Integer
    
    i = 1
    
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        
            WorksheetsArray(i) = ws.Name
        
            i = i + 1
    
    Next ws

For i = 1 To NumberOfWorksheets
    
        ValidationString = ValidationString & WorksheetsArray(i) & ","
    
    Next I
    
    ValidationString = Left(ValidationString, Len(ValidationString) - 1)
    
    Call List(Val:=ValidationString)

End Sub

Sub List(ByVal Val As String)
    
    Dim i As Integer
    
    For i = 1 to 100
            
            Cells(i, 4).Select
            
            With Selection.Validation
            
                .Delete
                
                .Add Type:=xlValidateList, _
                     AlertStyle:=xlValidAlertStop, _
                     Operator:=xlBetween, _
                     Formula1:=Val
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
                
            End With
        
    Next i
    
End Sub

 

[RoryA]

Has this got anything to do with the Data Validation being too long?

Thanks

Yes. The DV list is limited to 255 characters. If you exceed that, it will work while the file is open, but you will get that error as soon as you need to reopen it and your validation will be removed.

 

[tiredofit]

Thanks.

Any idea of a workaround?

 

[RoryA]

Putting the data into a range is generally the only practical workaround if you want to use DV lists.

 

[tiredofit]

Thanks.

You mean paste the contents onto the worksheet, then create the DV from that range?

Ideally would've wanted not to have to paste anything onto worksheets but if it has to be .....

 

[abbeyWigan]

Yes. The DV list is limited to 255 characters. If you exceed that, it will work while the file is open, but you will get that error as soon as you need to reopen it and your validation will be removed.

Rory, which version of Excel are you referring to? The limit is most definitely not 255 characters. I've just checked one of my previous projects done in Excel 2010 and managed to set validation with 186 lines of text with each line an average of 60 characters. That is 11,160 characters not including control characters (vbLf, vbCr).

The only time I had a problem was trying to exceed 32767 but then validation simply failed. On the other hand, if you set validation with volatile data, such as from an array, as you said, it would work until you quit but then fail next time you open the workbook.

I still suspect the problem is what I suggested to Tiredofit.

Regards,

Abbey.

 

[abbeyWigan]

Thanks.

You mean paste the contents onto the worksheet, then create the DV from that range?

Ideally would've wanted not to have to paste anything onto worksheets but if it has to be .....

That's good advise from Rory and it's not as hard as it sounds. Sadly I'm just going into a meeting now so can't do it for you. If this is still open when I'm done I'll take a quick look at it for you.

Regards.

 

[RoryA]

Rory, which version of Excel are you referring to? The limit is most definitely not 255 characters.

If you set a DV list string with a length greater than 255 characters, it will work while the file is open, but if you save and reopen the workbook, you will get an error message and the DV will be removed. I've just run a quick test to reconfirm this in 2010.

On the other hand, if you set validation with volatile data, such as from an array, as you said, it would work until you quit but then fail next time you open the workbook.

Not sure what you mean by that as you can't assign an array to a DV list.