Data validation, error on startup

Nervatos

New Member
Joined
Dec 19, 2021
Messages
32
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hello there.

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

VBA Code:
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:

Code:
<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?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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).
 
Upvote 0
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.

VBA Code:
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?
 
Upvote 0
Instead of creating & deleting the data validation, why not just put the list on a range? it would be much easier.
 
Upvote 0
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.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top