BUG: Errors were detected when saving, Grey window visual basic editor, corrupted excel files

Question

I have been running into a very annoying problem with my companies excel based system.

It started with a user encountering the following error:

https://imgur.com/a/kZ4tCrX

I searched the internet for this error and encountered the following threads:

https://learn.microsoft.com/en-us/answers/questions/53015/microsoft-should-fix-errors-were-detected-while-sa?orderBy=Newest&page=2

https://techcommunity.microsoft.com/t5/excel/bug-deleting-custom-number-format-used-in-conditional-format/m-p/2615306

This message I found interesting:

https://imgur.com/a/uxaV2Q7

Now the problem doesn’t stop with just this bug, our system works by having all the code in one main file. Other files in the system just open the main file and call code from there. It seems that when the main file is in a corrupted state (unsavable) and is called upon this also corrupts the file that is calling. This seems very similar to what a user specified in the second thead.

https://imgur.com/a/l0tQKHX

Back to the main file: When the main file is corrupt in a way that it becomes unsavable like the first error all macro’s are also completely unusable. In the following screenshot you can see that there are seemingly no macro’s in this workbook while there should in fact be more than 20.

https://imgur.com/a/kbIqGIg

Looking in the visual basic editor and trying to look at the code in the modules results in a grey window:

https://imgur.com/a/PhDoNc5

and no, this is not because the window is hidden somewhere it just doesn’t want show the code. This is most likely the reason why the file can’t save.

I have found a fix that can uncorrupt the file, it is as follows:

1. First open the corrupted file and select the option to disable all macro’s without notification in the trust center settings of excel.
2. Close the file and reopen again.
3. Go to visual basic and click on a random module with code in it, it now functions as normal again and you can see the code inside the module instead of the grey window like before.
4. Save the file and enable macro’s again in the trust center.
5. Close the file, open again and everything works as normal.

Now the problem is that this issue keeps coming back and is very much hindering workflow right now since Im not always there to help my colleagues out.

I need to find the origin of this problem so that I can permanently patch it out but up until now I’m not having much luck. I hope people here may have some insight in the problem.

I can’t be 100% sure about the code that causes this bug, but it seems to happen after code is ran, that deletes a row in a worksheet. This row has custom formatting applied to it so this might be the cause (Im currently testing this hypothesis).

Also one more thing: Sometimes the macro’s disappear in their entirety and sometimes they give an automation error when the file is in the unsavable state. Both issues are fixed with the same method I described above.

Thank you in advance.

Answer 1

Hi Chrismaster16,

Thanks for posting in the community. We are happy to help you.

According to your description, you have already tried some suggestions, but this problem still happens when using the main file. If your organization uses Microsoft 365 products, to troubleshoot the problem further, I recommend you contact your admin or IT department to raise a support ticket via Microsoft 365 Admin Center> Support> New service request. That support team there will have the correct channel and resources to check the situation remotely and find what exactly the reason has caused this situation.

Also, we welcome community members to share ideas about the situation if one has related experiences.

Sincerely, 

George | Microsoft Community Moderator