Data validation, error on startup

[Nervatos]

Hello there.

I have some problems with my data validation, I have try google - but can't find a solution to the problem.

Private Sub DropDownV4()
    Dim ws As Worksheet
    Set ws = Worksheets(Sh)
    Dim rng As Range: Set rng = ws.Range("B9")
    
    Dim myArray
    myArray = Array("Bundtet i luft" & Chr(130) & " på en overflade" & Chr(130) & " indfældet eller inkapslet", "Enkelt lag på væg" & Chr(130) & " gulv eller uperforeret kabelbakke", _
                    "Enkelt lag fastgjort direkte under et træloft", "Enkelt lag på perforeret vandret eller lodret kabelbakke", _
                    "Enkelt lag på kabelstige eller på holdere osv.")

    With rng.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=Join(myArray, ",")
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

I can save it and everything seems working. But then I start up the Excel file, then I get this error:

<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error040000_01.xml</logFileName>
<summary>Errors found in the file 'C:\Elektriker.xlsm'</summary>
<removedFeatures>
<removedFeature>Removed feature: Data validation from /xl/worksheets/sheet7.xml-del</removedFeature>
</removedFeatures>
</recoveryLog>

Some know, how I can fix this?

 

[Akuini]

Maybe this discussion helps you to understand the source of the problem. Something about max of 255 characters in data validation if you're not using an actual range as the source (check post #7).

Using UDFs in Data Validation List

Hello, I've created UDF that returns array of unique values from selected range. It works perfectly when entered in worksheet cell. When I hit F9 it displays all unque values as array. I wanted to use this function as source for Data Validation List but I get error "A named range you specified...

www.mrexcel.com

 

[Nervatos]

Hello Akuini

Sorry for the late answer.
I have tried this, to hope it would fix it. But it doesn't, don't think it's deleting the data validation. Cause it still return with error on startup.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ws As Worksheet
    Set ws = Worksheets(Sh)
    Dim rng As Range: Set rng = ws.Range("B9")
    rng.Validation.Delete
    MsgBox "Deleted."
End Sub

Maybe some know an smart way to fix it?

 

[Akuini]

Instead of creating & deleting the data validation, why not just put the list on a range? it would be much easier.

 

[Nervatos]

Cause it would still be over 255 as limit, or I'm wrong?

 

[Akuini]

Cause it would still be over 255 as limit, or I'm wrong?

It doesn't have the 255 char limitation, you can have thousands of row/item on the list.

 

[Akuini]

This is from Excel Data Validation Tips and Troubleshooting

Item Limit in Drop Down List​

There are limits to the number of items that will show in a data validation drop down list:

• The list can show up to show 32,767 items from a list on the worksheet.
• If you type the items into the data validation dialog box (a delimited list), the limit is 256 characters, including the separators.

 

[Nervatos]

Thank you for answer Akuini, I have made a list on range.