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.

 

[malcolmlyle]

We have a similar issue occur randomly on 1 user's computer in our office and we are trying to figure out why it does this. For this user, and only this user, it seems that (randomly) Excel will remove the forms/modules when the user saves the file. All other users in our office have no issues with this. We haven't been able to trace the cause.

Copy/pasting values from a workbook and/or rebuiding from scratch is not a viable solution for us. Luckily, this is a template used on nearly every project we have so we can always go back to get a new copy and start over. If the project was large this can be a lot of work, though.

WORKAROUND: We've had success doing this. Assuming your PC is not the one that is causing the VBA to be stripped out, on your PC do this ...

1. Open a functioning copy of the same workbook (XLSM or XLSB) that still has the forms/modules (maybe an earlier saved copy or a template)
2. Make sure that the "corrupted" workbook is still saved as XLSM or XLB type and that a user didn't save as XLSX type.
3. Open the "corrupted" workbook that has the forms/modules stripped out.
4. In the VBA Editor, copy the modules from the template/saved version to the "corrupt" workbook.
5. Close the template/saved version without saving.
6. Save the "corrupt" workbook, reopen, and see if it is fixed. In our case, this solves the issue, at least for this workbook.

You could even write a macro and keep it in the template to do this if you wanted.

I'd love to know what setting on this user's computer/Excel is causing this. We've searched for everything (trust access to VBA, enable macros, etc.) and can't find a cause. We've done a reinstall of O365 and "detect and repair" and still no solution.