Data validation removed after save to .xlsm file

[mikefenton]

Version: Office 365

I've got a workbook that I recently converted over from a .xlsx file to .xlsm. After I opened the file I had two sheets (one with UDFs added to cells, one without) have their data validation settings removed from cells. When I open the file I get the error:

"We found a problem with some content in '<Name of File>.xlsm'. Do you want us to try to recover as much as possible? If you trust the source of this workbook, click Yes."

Selecting Yes causes Excel to dump Data Validation from those pages. If I go in and re-added it, save again and re-open the same thing happens.

Each affected Data Validation was adding a DV Dropdown List to a cell (or group of merged cells). All of them used a range in another sheet as it's source and the range value was under 255 characters.

I'm not trying to change the sheet's compatibility for older versions of Excel.

I have 126 sheets in this workbook and most of the other cells with data validation that are nearly identical to the affected ones (in some cases referencing the same ranges) and those instances where not affected.

Any ideas why this might be happening?

[#2]

@mikefenton 

It is unusual for data validation settings to be removed from cells when saving a workbook as .xlsm, especially if the data validation was working fine before the conversion.

Here are a few suggestions to troubleshoot the issue:

1. Check for any macro or VBA code: If your workbook contains any macros or VBA code, ensure that they are not inadvertently removing or modifying the data validation settings. Review the code and make sure there are no conflicting operations.
2. Remove and reapply data validation: Try removing the data validation settings from the affected cells, save the workbook, and then reapply the data validation. This process can sometimes help fix any inconsistencies or corruption in the data validation settings.
3. Repair or reinstall Office: If the issue persists, it's possible that there might be some corruption or conflict within the Office installation itself. Try repairing or reinstalling Office to ensure a clean installation.
4. Test on a different computer: If available, try opening the workbook on a different computer with a different Office installation. This can help determine if the issue is specific to your computer or if it persists across different environments.

Remember to keep backups of your workbook before attempting any troubleshooting steps to avoid any potential data loss.