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. |
- Right click on any sheet tab and choose
View Code, to open the Visual Basic Editor. - In the Project Explorer at the left of the screen, locate the workbook.
- Locate a
Modulesfolder, and open it. (If there is noModulesfolder, go directly to Step 6.) - For each module in the
Modulesfolder:- Right-click on the module name.
- Choose
Remove [module_name]([module_name]is replaced with the actual name of the module) - Click
Nowhen asked if you want to export.
- Open the
Microsoft Excel Objectsfolder. - In order to clean each worksheet and
ThisWorkbook:- Double-click on the worksheet to open it.
- Press
Ctrl+Ato select all the code (even if it looks empty). - Press
Delete.
- Locate the
Formsfolder and open it. - Delete any
UserForms. - Locate the
Class Modulesfolder, and open it. - Delete any
classmodules that it contains. - Close the Visual Basic Editor.
- Save.
|
Other similar articles:
|
February 6th, 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.
February 7th, 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
March 10th, 2009 at 18:25
worked like a charm… thanks much!
March 12th, 2009 at 21:52
Great post but how does a rookie find the forms folder? Class modules folder?
Thanks.
March 12th, 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.
March 12th, 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.
March 13th, 2009 at 12:30
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.
March 14th, 2009 at 21:45
Ugly macro message gone!! Thanks for the screen shot. It was comforting to know I wasn’t loosing my mind.
April 15th, 2009 at 14:54
Thank you so much! This made me look much smarter than I am at work.
April 24th, 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.