Q: I have an Excel file and for some reason, every time I open it, it asks me if it’s OK to run macros … but there aren’t any! How do I stop this annoying message from appearing?
By Jeff Robson
This message means that somewhere in your file there must be some macros lurking. Possibly, a macro was recorded at some point, or a macro was created then deleted … but it’s in there somewhere … you’ve just got to find out where! Find it if you can! Ha ha ha.
Unfortunately Excel doesn’t give you a lot of help, and some of the places that macros live are downright obscure!
Excel allows macros to lurk in a variety of places such as behind UserForms, in Modules, in Class Modules, behind worksheets, and behind the workbook object itself.
To remove all macro code from a file you can either use some code kindly provided for free by Chip Pearson here or follow these steps:
- Backup your file.
- Press ALT-F11 to bring up the Visual Basic Editor (“VBE”).
- In the Project Explorer on the left-hand side of the screen, find the name of your workbook.
- Look for a Modules folder below this. If it exists, open the folder and right-click on each Module. Choose Remove ModuleName to delete each one and choose No when asked if you want to export first.
- Locate and open the Microsoft Excel Objects folder.
- Double-click each Sheet as well as This Workbook, to show the macros behind these. Press Ctrl-A to select any code that may exist and press Delete.
- Look for a Class Modules folder and open this if it exists. Remove any Class Modules that may exist.
- Look for a Forms folder and open this if it exists. Remove any UserForms that may exist.
- Save the file.
Your file should now be macro-free and should open without the annoying message. Yay! Just make sure you haven’t accidentally broken anything in the process!