The VBA programming language included with Excel allows you to create very powerful macros. It is not uncommon to record a couple of macros for a workbook, each designed to accomplish a quick little task. When you create the macros, Excel adds what is called a module to your workbook. This module is used to store the macros that you record or create.
You may notice that every time you open a workbook that contains macros, Excel asks you if you want to enable the macros. This is part of the security system built into Excel. (This system has saved my bacon on more than one occasion.) You may also have noticed that if you delete all the macros in your workbook, Excel still asks you if you want to enable macros when you later open the workbook.
Why would Excel do this? After all, you deleted all the macros in the workbook, right? The reason is that the module automatically created by Excel to hold your macros is not automatically deleted when you get rid of the last macro—it's still there. As long as the module is there, Excel will dutifully ask you if you want to enable your macros whenever you load the workbook.
To overcome this problem (and get rid of the macro prompt for this particular workbook), follow these steps:
At this point your workbook contains no modules, and you will not get any notification when you subsequently open it.
Of course, there is a technique that may be much simpler for your needs: Just use the Save As command and change the file type to an XLSX file. These workbook files cannot contain macros, so any macros that are in the current workbook are stripped out in the saving process.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10399) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Getting Rid of the "Enable Macros" Notice.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Creating lot of copies of a worksheet is a snap within a macro. This tip introduces a macro that will make three copies ...
Discover MoreIt is not uncommon to set variables in a macro based on other values, such as time or date. You could also set variables ...
Discover MoreYou may want your macro to clear the clipboard so that people cannot access anything left in the clipboard. That is ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2022 Sharon Parq Associates, Inc.
Comments