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]

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.



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

Rory,

Actually, you can. I did it. Here's a snippet from an old project of mine:

    With fdRng1.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertWarning, Formula1:=Join(tmp, ",")
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "BOM Maker: Main Stock"
        .ErrorTitle = "BOM Maker"
        .InputMessage = "Select header"
        .ErrorMessage = "Invalid value"
        .ShowInput = True
        .ShowError = True
    End With

"tmp" is an array. I had a few of these validations in the project and all were arrays. Because they were dynamic, to avoid the same problem earlier described by Tiredofit, I put a code in WorkbookBeforeClose to remove the validations before closing the workbook. If I didn't I got the same error message he reported.

 

[RoryA]

It's not an array when you put it in though - it's just a string.

I'm not really clear what you mean by dynamic here, as opposed to static.

 

[abbeyWigan]

It's not an array when you put it in though - it's just a string.

I'm not really clear what you mean by dynamic here, as opposed to static.

Point taken. It is always a string whichever way you do it. Even when passing a range, all Excel does is convert its contents. Point here is you CAN put an array directly into the expression for Formula1. It is dynamic because the data in the array is constantly changing in code. It is time variable (not static) hence my use of array in that particular project.

 

[RoryA]

Point here is you CAN put an array directly into the expression for Formula1.

Again, no not directly. It would be so much easier if you could, because you could use a UDF as the list source without falling foul of the 255 character limit, just like a range.

It is dynamic because the data in the array is constantly changing in code. It is time variable (not static) hence my use of array in that particular project.

To be honest I wouldn't call that any more dynamic than any other validation formula. In either case, the 255 character limit would apply.

 

[abbeyWigan]

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.

Tiredofit, Here's a code that works and you can play with. I built up an arbitrary validation string exceeding 30,000 characters then put it in a range and used it for validation.

Some general tips about your code:

1. Always use the directive "Option Explicit" or you might find debugging your code is a pain as the VBE ignores your variable typos and automatically declare them as new variables.

2. The code you wrote would have run very very slow (100x slower) compared with one below as you were setting validation one by one for each cell. You don't need to do this, you can set for a group of ranges in one go as I've done.

3. I know Excel does it but you don't need to Select a cell to set its validation. Doing so further slows you down if you're setting for many ranges.

I hope that helps.

All the best.

Option Explicit

Sub Start()
    Dim i%, tmp, rng As Range, ValidationString$
    
    Do 'Build up arbitraty string until length exceeds 30,000...
        ValidationString = ValidationString & "," & i
        i = i + 1
    Loop Until Len(ValidationString) > 30000
    ValidationString = Mid(ValidationString, 2)
    
    tmp = Split(ValidationString, ",") 'Convert it to array
    tmp = Application.Transpose(tmp) 'This changes the 1-D array to a 1x1 2-D array
    Set rng = ThisWorkbook.Worksheets("Sheet2").Range("A1:A" & UBound(tmp)) 'so that
    rng.Value = tmp 'we can dump it into a defined range.

    Call List(dtRng:=rng) 'Pass the range to your sub
End Sub


Sub List(ByVal dtRng As Range)
    
    Dim lst$, valRng As Range
            
    Set valRng = ThisWorkbook.Worksheets("Sheet1").Range("A1:A100") 'Define the range to _
    apply validation to.
    lst = "=" & dtRng.Parent.Name & "!" & dtRng.Address 'Define list formula
    
    With valRng.Validation
        .Delete
        .Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, _
        Formula1:=lst
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

 

[tiredofit]

Thanks, will give it a go.

 

[RoryA]

Nice. The only thing I'd add is quotes around the sheet name for safety.

 

[abbeyWigan]

Nice. The only thing I'd add is quotes around the sheet name for safety.

Good advice again.

Tiredofit, you may change the formula definition line to the following:

lst = "='" & dtRng.Parent.Name & "'!" & dtRng.Address 'Define list formula

A small detail I overlooked. The parameter "ByVal" in the List sub is redundant. Excel always passes range objects ByRef so you don't actually need it.

 

[RoryA]

Excel always passes range objects ByRef so you don't actually need it.

I hate to disagree again, but that isn't exactly true. Look at any worksheet event that passes a Range and you'll see that they are passed ByVal. Although you are still passing a reference (pointer), you are passing a copy of the original pointer when you pass an object ByVal, so you can still manipulate the object freely, but you cannot alter the original pointer.

 

[abbeyWigan]

I hate to disagree again, but that isn't exactly true. Look at any worksheet event that passes a Range and you'll see that they are passed ByVal. Although you are still passing a reference (pointer), you are passing a copy of the original pointer when you pass an object ByVal, so you can still manipulate the object freely, but you cannot alter the original pointer.

I cannot agree with that. You will have to give me a specific example before I can comment. In the meantime, a simple test:

Pass a range object ByVal. According to you, you're passing an object ByVal, so you can change it without affecting the "original" object?

Now change a property of your "ByVal" range object. Look on the worksheet. Yes, it has changed. That is not meant to happen with a "ByVal" parameter. So what does that tell you? What use would an object passed ByVal be?