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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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,099
Members
452,301
Latest member
QualityAssurance

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