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 for “Excel tip: Remove the ugly macro message

  1. February 6, 2009 at 13:22

    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. February 7, 2009 at 12:56

    You are most welcome. Simply, be sure to leave a link back to the original post here.

    I’m happy you found it useful.
    Yves

  3. March 10, 2009 at 18:25

    worked like a charm… thanks much!

  4. Dave
    March 12, 2009 at 21:52

    Great post but how does a rookie find the forms folder? Class modules folder?

    Thanks.

  5. March 12, 2009 at 22:00

    Dave, if you follow the steps, you will be presented with a short list of folders and you just have to look in the list of 2 or 3 folders.

  6. Dave
    March 12, 2009 at 22:15

    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.

  7. March 13, 2009 at 12:30

    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.

  8. Dave
    March 14, 2009 at 21:45

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

  9. Q
    April 15, 2009 at 14:54

    Thank you so much! This made me look much smarter than I am at work.

  10. April 24, 2009 at 19:35

    Thanks for taking the time to post this tip. You probably have no idea the number of people that you have helped.

  11. George Bailey
    April 22, 2010 at 00:31

    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.

  12. San
    July 15, 2010 at 08:12

    Many thanks .. same as above

  13. September 23, 2010 at 08:58

    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.