Add Type Error: Validatelist that exceeds 255 characters

[Kopko]

An error have been annoying me since I recently updated Excel.

I have a string that has more than 255 characters, and I was using .AddType to write in a cell like this:

formula = "a1,a2,a3,...,a200"  'string has near 600 characters
    With ws.Range("C8").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=formula
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Maybe someone could help me with that please?
Thanks a lot!

 

[Domenic]

Actually, the length of a string that consists of "a1,a2,a3,...,a200" is 891 characters. In any case, it was accepted when I tried it. I didn't get any errors. What type of error are you getting, and on which line? Did you assign your variable ws a worksheet object?

 

[Kopko]

Actually, the length of a string that consists of "a1,a2,a3,...,a200" is 891 characters. In any case, it was accepted when I tried it. I didn't get any errors. What type of error are you getting, and on which line? Did you assign your variable ws a worksheet object?

The string was just an example, sorry for the wrong count of characters...
I also was able to do this without any error in an old version of Excel.
Since it updated to Version 1901 (Office16), I'm getting the "RunTime Error 1004".

I believe that the error is related with the length of the string because if I minimize it to 250 characters my code works fine.

Thanks for helping me!

 

[Domenic]

I'm using Excel for Office 365 and had no issue when assigning a string having a length of approximately 8,000 characters.

 

[Kopko]

I'm using Excel for Office 365 and had no issue when assigning a string having a length of approximately 8,000 characters.

You tried to use the "Validation.AddType"?
Right now, I tried this code at my Office:

Sub test()
 formula = "AAA,BBB,AAA,BBB,AAA,BBB,CCC,DDD,EEE,AAA,BBB,CCC,DDD,EEE,AAA,BBB,CCC,DDD,EEE,AAA,BBB,CCC,DDD,EEE,AAA,BBB,CCC,DDD,EEE,AAA,BBB,CCC,DDD,EEE,AAA,BBB,CCC,DDD,EEE,AAA,BBB,CCC,DDD,EEE,AAA,BBB,CCC,DDD,EEE,AAA,BBB,CCC,DDD,EEE,AAA,BBB,CCC,DDD,EEE,AAA,BBB,CCC,DDD,EEE"  'formula with exactly 255 characters
   
    With ws.Range("C8").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=formula
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

The "formula" has exactly 255 characters and in this case it is working... But if I add one more character, I get the "Run Time Error 1004".

Like I said, my old a version was running fine with no errors. The error started when my office was automatically updated.

Maybe someone can have another solution for this?

Thanks!!

 

[Norie]

Couldn't you use a range in the data validation?

 

[RoryA]

I imagine they fixed it, since if you assign a longer string than 255 characters, the data validation will get removed when you reopen the file. You should use a range, as Norie mentioned.

 

[Kopko]

Couldn't you use a range in the data validation?

Sorry for my ignorance but how could I use a range in this case? with add type validate list?

Thanks a lot!

 

[Norie]

Put the values you want listed in the data validation in a list, let's say A1:A100, and use '=A1:A100' for the formula in the code.

    Formula = "=A1:A100"
   
    With Sheets(1).Range("C8").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=Formula
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With