Excel tip: Remove the ugly macro message

I believe that each and every Excel user already met the case where they open a file to receive a message dialog box announcing that there is a macro in the file and asking whether or not Excel should allow its execution. Unfortunately, in 99% of the cases, there is actually no macro and you may wonder how to stop this message (whatever you answer to the dialog box, it will come again and again).

But, today, I bring you the answer, the solution, the way out.

As a matter of fact, this is happening when in the Excel file there was a macro added, then removed. So, there is no longer a macro, but Excel does not know for sure. You need to help it decide. You can completely inform it of the total absence of macro. It’s better and relatively easy.

Warning: Always make a backup copy of the Excel file before you remove any code.

  1. Right click on any sheet tab and choose View Code, to open the Visual Basic Editor.
  2. In the Project Explorer at the left of the screen, locate the workbook.
  3. Locate a Modules folder, and open it. (If there is no Modules folder, go directly to Step 6.)
  4. For each module in the Modules folder:
    • Right-click on the module name.
    • Choose Remove [module_name] ([module_name] is replaced with the actual name of the module)
    • Click No when asked if you want to export.
  5. Open the Microsoft Excel Objects folder.
  6. In order to clean each worksheet and ThisWorkbook:
    • Double-click on the worksheet to open it.
    • Press Ctrl+A to select all the code (even if it looks empty).
    • Press Delete.
  7. Locate the Forms folder and open it.
  8. Delete any UserForms.
  9. Locate the Class Modules folder, and open it.
  10. Delete any class modules that it contains.
  11. Close the Visual Basic Editor.
  12. Save.

13 comments

  1. Thanks a lot for that great tip, guy!!!

    I was looking for this for a looong time (but with a portuguese search). I ask your permission to translate this to users from Brazil and other Portuguese readers.

  2. I’m trying to follow directions but knowledge gaps remain. Please help.

    Browser has ‘VBAProject (filesname)’ at the top of menu tree. Indented items under it are ‘Module1’, the sheet names and ‘ThisWorkbook’. No sign of the forms or class modules folders.

  3. excel_vba_workspace

    Dave,

    It just means that you have no Modules, nor Forms and you should not have the message at file opening announcing that you have macros to execute.

    It means that the file is already purged and you don’t need this tip.

    Or do you have the message still?

    This would mean that there is another possible cause that I do not currently know and should be investigated too.

    Let us know.

  4. Ugly macro message gone!! Thanks for the screen shot. It was comforting to know I wasn’t loosing my mind.

  5. I usually don’t post messages, let alone in response to one that is over three years old, but you literally just saved me from jumping off a bridge. Thanks.

  6. Wow, thanks a lot. I have found so many ways to solve this problem, but none of these worked.
    Now, I have tried out your version and it’s great. The problem does not longer exist!

    You’re awesome 🙂

Comments are closed.