Hi,
It's likely related to the way you're saving the workbook or the specific settings in your Excel application.
Here are a few suggestions from my side-
- Open the VBA editor by pressing Alt + F11, then go to the "Tools" menu and select "VBAProject Properties." In the "Protection" tab, ensure that the "Lock project for viewing" option is unchecked. This option can prevent changes from being saved in the VBA project.
- If you have any code in the workbook's "ThisWorkbook" module that clears or modifies the modules, make sure it is not interfering with the module preservation. Review your code to ensure there are no lines deleting or modifying the modules.
- Try repairing or reinstalling Excel: Try to repair or reinstall your Excel application. Sometimes, a corrupted installation can cause unexpected behavior.
Sometimes due to severe corruption as well, the modules may not work and even after these steps it may not work. At this point, you can try to repair Excel file using a tool.
Best Regards.