While working on an Excel workbook VBA Project and all modules are removed when saved.

[dgardineer]

I have an associate that is working in an Excel.xlsm file. They are mostly updating data in the sheets but do have 3 modules that contain a few macros. At some point the workbook is saved. When it is saved something happens to VBA Project. When the workbook is opened again, all the macros and sheet modules are removed. The associate (who doesn't know that this has happened) makes a bunch of changes to the sheets and then tries to save again and gets errors saying it's corrupted and cannot be saved. They then send me the workbook and I open it and go into VBA project and I see all the worksheets listed and the 3 modules in the project explorer. I then click on each of the sheets expecting the sheet modules to populate for editing. What happens is nothing. I try to click on each sheet and each of the 3 existing modules and nothing is populated to the right for editing. The only fix I have been able to come up with is repairing the workbook over and over and eventually it gets fixed and is able to save. I asked them to send me a listing of their Excel Add-ins but they look ok (Name Manager Utility, a content add-in, and solver add-in). I have the last 2 and have never had this problem. The last thing I suggested to them is to run Excel in Safe mode to see if it helps with the issue until I can come up with a better solution. My better solution is to ask you all if you have ever faced this issue and what is the fix or work-around. Thanks in advance for you help

 

[Jeffrey Mahoney]

File is corrupt. The unfortunate best solution to solve it once and for all is to do the following:
* Copy all values (no formatting) to a new workbook
* Format all your worksheets from scratch
* Copy all your code into the newly created modules in the new workbook
* Create all your named ranges from Scratch

Alternative:
* Save as a .XLS
* Convert all your Defined Excel Tables into ranges
* Save new WB to .XLSM
* Copy all your VBA code

I recently went through many options trying to fix a corrupt WB. It's not fun having to do it again after I thought I had fixed it.