Search code examples
excelvbaexcel-2019excel-365

Microsoft Excel repairs my .xlsm file and eliminates data validations on a sheet

I created an Excel utility, using Microsoft Office 2019, in which I use data validations, VBA code, named ranges and formatting.

It was working until one day I received an Excel prompt:
enter image description here

When I click on Yes, it gives me another pop-up where it says it recovered the file, and also gives me a link to the error log XML file. I click on it and open the .xml file using my default browser, and it shows the following details:

enter image description here

Looks like it is removing data validations from a particular sheet, and I realize that is true when I navigate to that sheet in the UI. To work around this data-validation removal, I created code that will re-instate all these data validations as required.

The problem arises when this Excel file is opened on a computer with Microsoft Office 365. Looks like it is removing not just data-validations but also other components like named ranges and buttons. There could be other things being removed, which I am unaware of. The macro to re-instate the data-validations is not sufficient.

Why does this problem arise? Why is a different version of Excel behaving differently? How do I solve this?

Solution

  • As rightly suggested by Ron Rosenfeld and e_conomics, the issue was with the data validation lists, whose sources were strings of comma separated values that were going beyond 255 characters. Apparently, that is a limitation with Excel.

    When I replaced the sources of data validation lists (string of comma separated values) with the ranges containing the corresponding values, the problem resolved itself. The repair dialogue never appeared again.