I’ve had a problem with Excel 2013 crashing a number of times when opening/editing spreadsheets with macros. When I click ‘Enable Content’ to enable macros, the Excel window fades out, goes unresponsive and then crashes with a glorious “Microsoft Excel has stopped working, Windows is checking for a solution to the problem” message. Unfortunately Windows never seems to be able to find the elusive solution that we need.
I’ve found that the spreadsheets tend to stay working whilst you’re looking at them (if you don’t click ‘Enable Content’), however it crashes when you try and do anything useful such as copy/paste, etc. Sometimes Excel just crashes when opening the file, even without enabling macros. This is seemingly caused by a corrupt compile of the macros. Yes I know it’s VBA code which is interpreted and not compiled, but I’m over-simplifying here.
There a a couple of solutions to this:
1) Before enabling the macros try adding a new blank sheet, and then save the spreadsheet, reload, and sometimes it corrects itself. But not always.
2) Recompile the macro
- Open the Excel spreadsheet (but don’t enable macros yet)
- In the ‘Developer’ ribbon, click ‘Visual Basic’
- On the ‘Tools’ menu, click ‘Options’
- Then on the ‘General’ tab, untick ‘Compile On Demand’, then click OK
- Then scroll down in the ‘Project’ window, right click on ‘Modules’, then Insert a new module
- Then just close the Visual Basic window, save the spreadsheet and close Excel.
- Hey presto, when you then load your spreadsheet you’ll be able to enable macros, and Excel won’t crash.
Thanks to Paulina from Microsoft for helping me find this solution – it’s saved my bacon a few times now!
Frog-Blog-Out
Thank you very, very much!
Thanks – a useful tip. Better than others out there to achieve the same end.
Thank you so much! Almost had to redo a very large file.
Thank you, this worked. Was receiving a visual basic error 400. Now it is fixed!
You are amazing! Thank you SO MUCH for this. I’m glad Google ranked you #1 when I searched for how to fix this. You really saved me a lot of time!!
Thank you for this solution. It worked for a massive file that I thought that I had to abandon.
Thanks, solution 1 worked!
Thanks – much appreciated this worked!
God.Bless.You
YOU ARE A LIFESAVER!!!!! seriously!!! THANK YOU!!!!!!
Happy to help 🙂
Hi Alex,
Just tried this with no luck 🙁
I already have a Module with a Macro (Refreshing a Data Connection and a few other actions), by adding another Module doesn’t seem to help. Any ideas?
Like always! Real help!
This has saved my large file on many occasions.
Unfortunately, it is good for about 3 – 4 times of opening the file and then has to be done again. This issue seemed to start when Microsoft pushed out updates; it’s an old file, but has always been stable until updates the past couple of years. Anyone else had this problem or know any ways to correct it permanently? Rebuilding my excel program every year is extremely time consuming and complex. (And yes, we have tried the repairing excel and completely re-loading excel).
Thanks!!!!
Hi Sean
I’ve also found that the latest Office 365 version of Excel is far more robust and can often fix this problem, so give that a go if I were you.
Regards
Alex
Solution #1 worked for me, thanks very much.
Adding a sheet and resaving worked for me.
You are super BRILLIANT!!!!!! I almost started re-building my ultra-large Workbook!!! Thank you!!
I am having this problem using Office/ Excel 2011 on a Mac. Any suggestions?
Thank you!
I have an issue where enabling content (enabling macros) causes the workbook to close. We have 3 files all of them have the same behaviour all of a sudden. For months together it has worked just fine. I am guessing it would be some updates that may have caused it. However trying to get to see if we can fix it in Excel somehow. I tried the above steps outlined however this did not resolve the issue. Any suggestions?
dear sir i am unable to working the above tips in a macro sheet because when i open any macro work sheet the exel has stoped working before enable macro
Hey Alex
From one day to another I got the problem. I use Excel 2016 and Office 365. I have tried to repair Office, I have cancelled the whole Office package and re-installed it. Did’nt help !
Now I have tried your solution, but as soon I want to save the file, Excel hangs up and is not responding. So the file is not saved.
Any suggestions?
Regards
Jorgen S.
If you can open the file, I’d just try copying/pasting into a new document. If that doesn’t work then you may have to contact Microsoft support to get them to help out.
Thanks
Alex
I’m having this exact same problem with saving. Did you find a solution to this?
I also have the same trouble with the new update of 365, version 1609. Please help!
http://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10/o365-issue-after-latest-update-with-macro-enabled/90a3d408-ab6b-4890-9fab-2cc0287ad41d
Here explains why the 1609 is the problem. It can’t be solved with this solution.
For anyone having an issue with build 7369.2038, there is currently no solution.
The current workaround is to rollback to the previous version of Office 365. https://support.microsoft.com/en-us/kb/2770432
Thanks Alex!!!
Thanks! you saved my life (and my job)
Savior! Thank you
Thanks a bunch! This was very helpful and easy
It worked beautifully!
Thank you for sharing.
You just earned 500 life karma points.
This has worked very well for me…but can anyone explain why? I have noticed that I can open the same file on several different computers and some excels crash while others do not’, leading me to believe that it is based on some individual settings on each computer.
I’m wondering the same. I’ve created several macro-driven tools for use across our company nationally and some folks have crashing issues and others do not. Users have Windows 7, 8 or 10 and have either Office 2010 or 2013, 32 bit. A file will work fine for me and others on our machines but if I email it to some Excel will crash upon them attempting to open it. I can fix it for them by following the suggestions in this article but I don’t want to keep having to do that for them every time there’s and update to the tool. I also have an auto-update system in place where the latest version is stored on a SharePoint site but when a user updates they’ll have the crashing issue again.
Thank-you! Adding the blank sheet before enabling macros worked for me. I had already spent a couple of hours with other troubleshooting steps before finding your suggestion and would have spent a few more.
Dale
The cause of my problem was that I had my own function in a lot of cells that checked if a certain file exist. When I suddenly lost my permission to a certain folder where one of the files was it just crasched without entering debug. Maybe it can help someone!
In Excel 2010 files were working. In Excel 2016 – files crash. It was due to cells defined as NUMBER. When I open file in Excel 2010, change cell formatting from NUMBER to TEXT, save the file, open it in Excel 2016 – then Excel 2016 is not crashing anymore.
Update: it was because in Windows 10 -> Control Panel -> Clock, Language, and Region -> Change date, time or number format tab: Formats -> Format = English (United States) and in Excel 2016 -> Cell property -> Cell data type = Number and when trying to activate: Use 1000 separator (,) . I have changed Windows Format to German (Germany) and was able to activate Use 1000 separator (,) without Excel 2016 crashing.
Many thank for this. You’re a star. It seems to work for me.
Can you please explain what the consequence of unticking the Compile on Demand box is? Will this affect the future behavior of the file? Thanks again.
Hi Dan
It just changes the timing of when and how the code is compiled, it doesn’t change the functionality. By unticking this box the code is compiled all at once, instead of a bit at a time as needed. This should therefore actually have a small positive impact on macro runtime performance.
Alex
MS Access crashes after enabling macros, please help me to solve the problem.
Hi,
I cannot get to the point to even try either of the two solutions above. Once I open the workbook, the error message “Microsoft Excel has stopped working, Windows is checking for a solution to the problem” comes up straight away. Any tips on how best to tackle this?
Apologies if there is already something in the thread around this.
Thanks,
Amy
Hi Amy, can you open the document in Excel’s safe mode? (hold CTRL whilst opening Excel)
I get this problem quite often.
To expand on Alex’s solution: I open in Safe Mode, then save as a new file (this now allows editing). Then I do a manual compile, and then I save as a new file again.
When I re-open the saved file it is now (a) not in Safe Mode and (b) doesn’t crash upon Open.
Quite what this song-and-dance does I don’t really understand, but it works for me.
Hello All,
Need help on one thing, Whenever i open Excel which containing macro an error occurs as “Security Warning Macro have been Disable. Enable Content” . After clicking on Enable content button the excel open in good manner.
Now I have to disable any cell value or change color to while, let say cell F5 having text “Best of Luck” which is in black color. I record the macro as to change it to white color/it should be disabled. OK.
My Question is, whenever we click on Enable content button, the text color “Best of Luck” of cell F5 should be change to white/ disappear.
So How to do, need help.
I love you.
Another solution that I found is to open the file in Excel 2010 (after copying to a machine with 2010), enable macros there, save it, and copy it back. I’m not sure why this works, but it may be due to the fact that 2010 has a different file structure and by loading it in 2010, saving it, and loading it back in 2013, it corrects the corruption.
Having a machine with an older copy of Excel has saved me a lot of times before I found these other solutions.
Great tip, thanks
One thing that I forgot to add is that (as far as I can tell) this works even if you have used new formulas or features in 2013 that do not exist in 2010. I meant to add that for those who might be worried. The newer formulas won’t work in 2010, but it has no trouble loading the worksheet formulas, macros, etc., that contain the new features and saving them back.
thanks heaps. Solution worked for me
When I encounter this issue I usually just open up the vba screen, click on the “debug” tab, click “compile vba project” then save the file.
Amazing that it works … but thank you!
Thank you !!
Alex – Can I go back and reselect the Compile on Demand box? Or is there no need
Once the macro error is cleared then yes you can, although there’s no real need
Are there any best practice tips for cleaning up after VBA macros. I often find the excel crashing behaviour is when I open a workbook after a macro in another workbook has completed running. It feels like something is still active. Are there any tear down commands or release memory commands that we should try and build in as standard? I am adding an on-open macro to convert calculation to manual as I think this can sometimes cause issues if you have a VBA function being called within a workbook cell.
Thanks for any advice.
Thank you very much! Solution 1 worked for me.
1,5 weeks’ work saved. much thanks <3 (tip1 solved my case)
Files containing macros and that linked to other worksheets crashed Excel in vbe7.dll. They would work in 32-bit Excel.
We were able to fix the issue by following these MS articles:
https://support.microsoft.com/en-us/help/3085435/august-8-2017-update-for-excel-2016-kb3085435
https://blogs.technet.microsoft.com/the_microsoft_excel_support_team_blog/2012/09/07/excel-crashes-in-vbe7-dll-running-or-enabling-macro/
The following registry changes were needed:
~~~~~~
HKEY_CURRENT_USER\Software\Microsoft\VBA\7.0\Common
Type: DWORD
Name: CompileOnDemand
Value: 0
~~~~~~
HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options\
Type: DWORD
Name: ForceVBALoadFromSource
Value: 1
~~~~~~~
You can perform the same changes by running these 3 commands:
Reg.exe add “HKCU\Software\Microsoft\Office\16.0\Excel\Options” /v “ForceVBALoadFromSource” /t REG_DWORD /d “1” /f
Reg.exe add “HKCU\Software\Microsoft\VBA\7.0\Common” /v “CompileOnDemand” /t REG_DWORD /d “0” /f
Reg.exe add “HKCU\Software\Microsoft\VBA\7.1\Common” /v “CompileOnDemand” /t REG_DWORD /d “0” /f
Thanks alot! Solved the problem!
Omg thanks! I kept redoing my work from previous versions of my files that can be saved without problems and this saved me from having to do that every time my workbook gets corrupted! Thanks so much!
I’ve had a problem with Excel 2016 crashing or close excel, when record macros or click on module. we need help.
This worked perfectly. I’m back off the ledge. Awesome resource, thank you!
Thank you so much! Helped a lot
Heck yeah! I appreciate you sharing, this worked perfectly.
Thanks a lot. Solution 1 worked:)..Super…
After more than 3 years since this post was written in Nov-2018, the first tip saved my life. Thanks!
Dude, this is ****** amazing! You helped lot of people here 🙂
Became crazy because I have this problem since 1 week now (Stop working when enabling content), a nightmare.
Again thank you so much !
Thanks a million. Solution 1 worked and saved me 7 hours of work.
Solution #1 works!!!
Solution 1 worked! Was being told to refer to office help desk due to security system error and then decided to google as have nothing to lose. So appreciated.
Thanks for such a beautiful post, very informative and useful article
First tried Solution #2 – added new module and save&close. It worked. Thanks great advice.
Side thought: why does this supposed to be like this… Microsoft must fix these kind of bugs… sigh… there are many things that must be solved in not the way it supposed to be…
Recompile the macro worked. Thanks!!
Just like magic 🙂
AMAZING THIS WORKS!
I have spent 1-2 hours trying to save my spreadsheet and this works!
Alex,
I hope this note finds you well. Your solution saved me and my compadre a nights… NAY! many nights of sleep!
you da man, keep on keepn’ on,
It works !! I am a consultant. This helped me out of reworking a 200 MB file with more than 50 macros and complicated relations and formulas
Worked like a charm.
Care for an explanation though?
Great help! Thank you 🙂
Thank you so much! Option 2 did the trick 🙂
Option 1 worked this is a god tier post
Didn’t work for me. My issue was related to content manager being unstable. you get an additional option on the ribbon near help that says ‘content manager’. Click on that then the help button. A popup will appear that allows you to disable content manager. Excel will reopen and the file should open.
Its really helpful
Wow, it looks as if this solves the problem that really drove my crazy. Can’t test for very long now, because I have to stop now to recover from the stress 😯
Thanks a lot. I’ll save this page to my bookmarks and then backup the bookmarks so I can always find this when it happens again!!
I was too optimistic, crashes are there again and nothing seems to help
Thnk you so much……
Thank you!!! You save my life!!!
Thanks for the great solution.
First solution worked for me. Many thanks you are a “life saver”. Cheers Bob
Worked again this morning. Question – How did you ever come up with this solution, chance or is there a logic. Thanks again Bob
Hi Robert.
It was a long and frustrating process! Like all troubleshooting, it’s a process of continuous testing to narrow down the issue, and a fair bit of trial, error, click and hope!!
The original spreadsheet in question was of some importance, so worth spending some time to fix – and got there in the end!
Alex
Hi Alex
Today on opening my very large spreadsheet I got a “Can’t find project or library” error.
Consulted Mr Google, didn’t really understand what it was telling me what to do. Had a poke around but achieved nothing. Then I had a thought, probably wont work but worth a try. So did the old “add a blank worksheet” trick and BINGO it’s working fine.
(This spreadsheet is very important (vital) to the running of our business as it tracks and predicts cashflow)
So you are due another big thank you.
Cheers Bob
Another one of those heart stopping moments!! Glad you got it back again!
Wow, thank you so much, really appreciate it!
May God bless you always. Thank you for saving me..
This actually worked
First solution with adding a new blank worksheet in fixed it!
THANK YOU SO MUCH!!!
You are a gentleman and a scholar good sir and enabled the largest Healthcare company in the US to work today
Always happy to help! 🙂