In Microsoft Excel, the use of VBA Macro code is a very common phenomenon among its users. But it sometimes gets inaccessible when we open it. We cannot run the macro code the way it should be done. There are numerous solutions for this occurrence. In this article, let us solve when macros not working in excel with 3 possible solutions.
Download Practice Workbook
Download this sample file and practice by yourself.
3 Possible Solutions When Macros Not Working in Excel
To describe the process, we prepared a sample dataset here with the First Names, Last Names and Ages of 6 persons.
- Now, we want to copy a single cell from the dataset with this code.
Sub Copy_Range()
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveSheet.Paste
End Sub
- After typing the code, when we press F5 on the keyboard to run it, an error message pops up that states that the macros code is not working for the excel file.
To solve this, follow the solutions below and you will successfully enable macros in Excel.
1. Enable Macros for Single Workbook in Excel
If you want to run macros for a single workbook, then follow the solutions below.
1.1. Access via Security Warning
We can enable macros through the Security Warning. Any of the following incidents may happen.
- First, when you open the workbook, you will notice a Security Warning on the top of the Formula Bar.
- Here, click on Enable Content to enable macros.
- Otherwise, you will get this Microsoft Excel Security Notice if the Visual Basic Editor window is opened during the time of opening the file.
- Therefore, click on Enable Macros to access it.
Read More: [Fixed!] Unshare Workbook Greyed Out in Excel
1.2. Turn On with Backstage View
The Backstage View of any excel file is also very efficient to enable macros. To do this follow the steps below.
- First, go to the File tab of your excel file.
- Then, in the Backstage View on the left side, click on Info.
- Lastly, select Enable Content under the Security Warning.
- That’s it, the macros will now work swiftly.
1.3. Change Settings of Trust Center
We can modify the setting in Trust Center to run macros properly. Follow the steps to do the task.
- Firstly, go to the File tab and select Options from the Backstage View.
- Secondly, select Trust Center in the Excel Options dialogue box.
- Thirdly, click on Trust Center Settings.
- Fourthly, go to the Macro Settings in the Trust Center dialogue box.
- Following, select the Macro Settings option as Disable VBA macros with notification.
- Afterward, go to ActiveX Settings and select the option shown in the image below.
- Lastly, press OK twice to close all the dialogue boxes and afterward you will successfully run the macro.
1.4. Establish Trusted Documents
You can run macro in a single workbook by making it a trusted document. Let’s see how to do this.
- First, go to the File tab and select Options.
- Then, select Trust Center Settings from the Trust Center tab in the Excel Options window.
- Afterward, mark the Allow documents on a network to be trusted option in the Trusted Documents section.
- Lastly, press OK twice to close all the windows and therefore, the macros will work properly.
- Additionally, if you need to omit this feature, then simply click on Clear in the Trusted Documents tab and close the window.
2. Allow Macros Permanently in Excel for All Workbooks
So far, we learned the process to enable macros for a single workbook. But you can also enable it for several workbooks as well. Here are the solutions to this.
2.1. Apply Changes in Trust Center
Changing the settings in Trust Center will successfully enable macros permanently. Let’s see how it works.
- First, select File > Options.
- Then, select Trust Center > Trust Center Settings.
- Next, go to the Macros Settings tab.
- Here, mark check the Enable VBA macros option.
- Lastly, press OK twice to close all the windows.
- That’s it, now try to run the macro and you will see it is working swiftly.
Read More: [Solved]: Macro Settings Greyed out in Excel
2.2. Create Trusted Location
You can create a separate folder for reliable workbooks and run macros without any security warning. For this, go through the process below.
- In the beginning, select File > Options.
- Then, go to Trust Center Settings from the Excel Options window.
- Afterward, go to the Trusted Location section in the Trust Center window.
- Here, click on Add new location.
- Next, insert the required folder in the Path section.
- Along with it, type a narrative in the Description box to determine the trusted files easily.
- Additionally, you can mark check the Subfolders of this location are also trusted option if you have any.
- Finally, press OK twice to close all the windows.
- Now, following this process add as many files as you want.
- Therefore, you will easily run the macros of these files without any interruption.
Similar Readings
- [Fixed!] Excel COUNTIF Function Not Working for String “True”
- [Fixed!] Excel Hyperlink Keeps Coming Back (5 Quick Solutions)
- [Fixed!] Excel Scroll Bar Too Long (5 Solutions)
- Excel Data Validation Greyed Out (4 Reasons with Solutions)
- [Solved:] Excel Not Filtering Entire Column (3 Quick Fixes)
3. Turn on Macros for One Session
If you want to run macros for a single time and return to the original state when you reopen it, you can follow this process below.
- First, go to the File tab of your excel file.
- Then, go to the Info section in the Backstage View.
- Here, select Advanced Options in the Security Warning.
- Following, select the option Enable content for this session.
- Lastly, press OK and you will be able to run the macro only for that period of time.
How to Disable Macros in Excel
As we know various methods for enabling macros, we should also get acknowledged on the process of disabling them. We may need to disable macros sometimes without notification. To do this, follow the process below.
- First, select File > Options.
- Then, select Trust Center > Trust Center Settings.
- Following, go to the Macro Settings.
- Afterward, select the option Disable VBA macros without notification.
- Lastly, press OK and you will successfully disable the macros.
Things to Remember
- You must save your excel file as Macro-Enabled Workbook. If you save it in any other extensions, no matter whatever solutions you try that we described above, the macros will not work.
- It is suggested to avoid giving consent to Enable all macros in the Trust Center window. Otherwise, it may result in enabling non-trusted sources.
Conclusion
Finally, we are at the end of our article on 3 possible solutions when macros not working in Excel. Let us know if you have any suggestions regarding this. Keep an eye on ExcelDemy for exciting tutorials.
Related Articles
- [Fixed!] Merge Cells Button Is Greyed Out in Excel
- [Fixed!] Formulas Are Not Calculating Automatically in Excel
- [Solved!]: Excel Page Layout Being Greyed Out (4 Quick Fixes)
- [Fixed!] IF Function Is Not Working in Excel (4 Quick Solutions)
- [Fixed!] Auto Fill Options Not Showing in Excel
- [Fixed!] Excel Scrolling Too Many Rows (2 Easy Solutions)
- [Fixed] QUADF Not Working in Excel