Roumazeilles.net

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.

Posted

in

,

by

Yves Roumazeilles

Tags:

Comments

13 responses to “Excel tip: Remove the ugly macro message”

  1. EduBravim Avatar
    EduBravim

    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. Yves Roumazeilles Avatar
    Yves Roumazeilles

    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. Ray Kostiuk Avatar
    Ray Kostiuk

    worked like a charm… thanks much!

  4. Dave Avatar
    Dave

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

    Thanks.

  5. Yves Roumazeilles Avatar
    Yves Roumazeilles

    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 Avatar
    Dave

    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. Yves Roumazeilles Avatar
    Yves Roumazeilles

    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 Avatar
    Dave

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

  9. Q Avatar
    Q

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

  10. Ed Collins Avatar
    Ed Collins

    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 Avatar
    George Bailey

    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 Avatar
    San

    Many thanks .. same as above

  13. Marvin Avatar
    Marvin

    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 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.