Macros allow you to automate many time-consuming tasks and also create some functions and functionalities that are not already built-in in Excel. As a part of the security measure, if you receive a file that has a macro, Excel will disable it by default and you will have to manually enable the macros in the file so that they can be used. In this tutorial, I will show you how to enable macros in Excel and all the important things that you need to know about it. So let’s get started!
Before Enabling Macros – A Word of Caution!
Macros are amazing, they are a huge time saver, but they can also be dangerous (not always, but sometimes). If you get a file from someone or you download it from the web, you shouldn’t just go ahead and enable macros on these files. There is a possibility that there could be malware or a damaging macro in the workbook. To give you an example, it’s possible for anyone to create a macro that, if enabled, can add malware in your system, or delete some files/folders that are important for you. To avoid any issue that can be caused because of a macro, Excel has a default setting to keep macros disabled. As the user, the onus is on you to make sure that there is nothing wrong with the file that you’re trying to open. So every time you try to open a file that has a macro (no matter how big or small), Excel will show you a warning (a yellow bar) and you will have to enable the macros yourself.
So even if you find it irritating and a waste of time to enable macros always, this is a necessary evil. Now, let me show you some methods you can use to enable macros in Excel. You can choose to enable the macro for one-time use only, permanently enable all macros in a specific file, or specify a trusted location and all the files in that folder would have the macros enabled by default. Let’s get into each of these scenarios now.
Enable All Macros in a Specific File (Permanently)
If you have got a file from a trusted source and you want to enable all the macros in the file, you can do this using two methods. Both of these methods will enable the macros permanently for the given file, which means that the next time you open it, you be asked to enable the macros again.
Using the Yellow Security Warning Bar
As soon as you open a file that has a macro, you would see a yellow warning bar as shown below. If you’re sure that the file is safe and the macros in it can be run, all you have to do is click the ‘Enable Content’ button. As soon as you do this, the yellow security warning bar goes away and macros are enabled on this file. What also happens in the backend is that this file is now marked as a trusted document by MS Excel, and the next time you open this file, you won’t see the yellow security warning bar. You can also choose to close the yellow warning bar by clicking on the x in the right-most part of the bar. When you click the close icon, the macros remain disabled. In case you take any action that needs a macro to run (such as clicking a button that runs a macro or making a change that triggers a macro), you will see a warning as shown below:
Using the Backstage Options
There is another way in the backstage area that you can use to enable macros in a specific Excel workbook. Below is how to do this: This will also mark the file as a trusted document and enable all macros in the file. Also, the next time you open this file, it won’t ask you to enable macros again.
Enable Macros in a Workbook for One Time Use (Temporarily)
Excel also allows you to enable macros for one session only, so that the next time you or anyone else opens the file, they would again be asked to enable macros again. This can be quite useful if you have files in a network drive and many people have access to it. Below are the steps to enable macros for one session only: The above steps enable the macros for the current session, and as soon as the file is closed, the macros are disabled again.
Enable All Macros in All the Excel Files
There is also an option that will enable all the macros in all the files in your system. This means that the next time you open any file that has macros, these would be enabled by default. While you can do this, I (and the Microsoft Excel team) strongly suggest you don’t do this, unless you’re absolutely sure that the Excel files you have (or may get in the future) will be safe. In case you feel it’s safe to enable all macros in all the Excel files, below are the steps to do this: Once done, the above steps would enable all macros in all the files. This means that you won’t see any warning when you open any Excel file that contains macros (which can be unsafe and dangerous). Again, if you know what you’re doing and you’re sure the files you work with are safe, you can use this option. Let me also quickly explain the other option that is available to you (that you see in step#7)
Disable VBA macros without notification: When this option is selected, all the macros are disabled by default, and you won’t see any warning or prompt to enable macros. You will still be able to use macros that are in the trust documentsDisable VBA macros with notification: This is the default option, where the macros are disabled, but you still see a prompt when you open a file (which is not a trusted document) that contains a macro. You can enable these macros as shown in the methods aboveDisable VBA macros except digitally signed macros: Just like you can make an Excel file a trusted document, you can also add trusted publishers. When this option is selected, it will only allow macros that have been signed by a trusted publisher.Enable VBA macros: This enables all macros in all the Excel files
Enable Macros in Files in a Specific Location/Folder
Excel allows you to specify trusted locations. Excel files stored in a trusted location will automatically have the macros enabled and you won’t see the warning that you usually see. Even if you have disabled all macros as the global setting, the ones in the files in the trusted location would still work. This can be quite useful when you don’t want the macros to work in any file, except the ones that are stored in a trusted folder/location. For example, if you have some templates or dashboard that you use often and it contains macros, then you can continue to use these uninterrupted, while the macros in all the other files would be disabled and would need to be enabled on a case by case basis. Below are the steps to see the current trusted locations and add a new one: The above steps would make the specified folder a trusted location. In case you want to delete or modify any of the existing trusted locations, you can do that as well.
Clear All Trusted Documents
As I mentioned earlier, when you enable macros in a workbook in Excel, in the back end it is marked as a trusted document. This means that the next time you open this file, you will not see the security warning and the macros would be enabled by default. Unfortunately, as of now, there is no way for you to remove an Excel file as a trusted document (i.e., mark it as an un-trusted document). However, there is an option where you can remove all the trusted documents in one go. Below are the steps to do this: The above steps would clear the trusted document, and now any file that you open that contains macro would show you the security warning. In case you do not want any excel file to be marked as a trusted document when you enable macros in it, click on the ‘Disable Trusted Documents’ option after Step#7. When this option is enabled, anytime you enable macros for an Excel file, it would be for that session only, and you would again see the security warning the next time you open the same file. Note that this will not affect the documents that are saved in the trusted locations. Those Excel files Would continue to open with macros enabled in them. There are some folders that are marked as trusted locations by Excel, and all the files in these folders would have the macros enabled by default. For example, the XLSTART folder or the Templates folder are already marked as trusted locations. Similarly, if you create a Personal Macro Workbook that contains macro codes, it will be stored as in the XLSTART folder and you would always be able to use macros in the Personal Macro Workbook, even when you have disabled the macros in the Excel files So, these are all the methods that you can use to enable macros in Excel. Having a good understanding of all the settings would make sure that your work is safe and you don’t accidentally end up opening files and macros that can harm your work/system. And to reiterate again, Microsoft had good reasons to keep all the macros disabled by default, and I strongly recommend you keep that setting intact and only enable macros in Excel documents that you know for sure are safe. I hope you found this tutorial useful! Other Excel tutorials you may also like:
How to Remove Macros From an Excel WorkbookHow to Assign a Macro to a Button in Excel (Easy Guide)How to Record a Macro in Excel24 Useful Excel Macro Examples for VBA Beginners (Ready-to-use)How to Run a Macro in ExcelHow to Automatically Open Specific Excel File on StartupHow to Recover Unsaved Excel Files [All Options + Precautions]
