[Fixed!] Macros Not Working in Excel (3 Possible Solutions)

Get FREE Advanced Excel Exercises with Solutions!

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.

Macros Not Working in Excel

  • 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.

Enable Macros for Single Workbook in Excel

  • 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.

Enable Macros for Single Workbook in Excel

  • 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.

Enable Macros for Single Workbook in Excel

  • 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.

Enable Macros for Single Workbook in Excel

  • 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.

Allow Macros Permanently in Excel for All Workbooks

  • 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.

Allow Macros Permanently in Excel for All Workbooks

  • 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


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

Tags:

Sanjida Mehrun Guria

Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo