While using Microsoft Excel, to execute activities fast and cut down on typing time, macros are enabled. Such activities may include data formatting, developing predefined programs, using specifically created functions, and other activities. But sometimes we may face a problem with our Excel files that are macros enabled but not working. In this article, we will demonstrate some reasons and their solutions to fix macros-enabled Excel files not working.
[Fixed] Excel Macros Enabled But Not Working: 5 Reasons with Solutions
Although VBA programs are incredibly successful at automating tedious and complicated operations, they pose a serious security risk. Unintentionally using a malicious macro might harm Microsoft Office installation, screw up files, or even totally destroy items from the hard disk. Excel’s default option disables all macros with notifications because of this.
Reason 1: Worksheet Might Be Saved As .xlsx File
Excel files divide into two categories since Excel 2007. The file extension for spreadsheets that support macros is .xlsm, whereas that of unmodified worksheets is .xlsx. We can find macro files in workbooks that support them.
If you shared a worksheet with another user that had macros enabled, then the macros stopped working later, it’s conceivable that they unintentionally re-saved the file as a regular workbook without macros.
Solution: Save File As .xlsm Extension
To solve the problem, you have to save the file as a .xlsm extension. To do this, follow the instructions below.
STEPS:
- Firstly, go to the File tab from the ribbon.
- Thus, you can view the backstage menu of your workbook.
- Secondly, click on Save As.
- Thirdly, select Excel Macro-Enabled Workbook(*.xlsm) from the file extension drop-down menu.
- Finally, clicking on the Save button will save your file as .xlsm extension.
Read More: [Fixed!]: Unable to Enable Macros in Excel
Reason 2: Excel May Automatically Disable Macros
Excel typically disables macros and prompts you to confirm whether they allow when you first access a spreadsheet with macros that you can’t produce. When a worksheet has macros enabled but originates from a source you don’t trust, you should never enable the macros. Your system may harm by macros, which can even give rise to macro infections. Not always, this notice will show. Users who are really concerned about security can totally deactivate macros using Excel’s settings.
Solution: Change Global Macro SettingsÂ
We can change the global macro settings to fix the problem. Let’s follow the procedures.
STEPS:
- To begin with, go to the File tab from the ribbon.
- This will take you to the backstage menubar.
- Then, select the Options menu.
- Thus, the Excel Options dialog box will appear.
- Now, go to the Trust Center option and click on Trust Center Settings…
- Further, click on Macro Settings from the Trust Center dialog box.
- Select Enable VBA macros (not recommended; potentially dangerous code can run) from Macro Settings options.
- Make sure the Enable Excel 4.0 macros when VBA macros are enabled and Trust access to the VBA project object model are checked.
- Furthermore, click on the OK button to complete the procedures.
- This will again take you to the Excel Options dialog.
- To finish it and save the changes click on the OK button.
Read More: [Fixed!] Macros Not Working in Excel
Reason 3: Personal Macro Workbook May Contain Macros
You can save a macro either into the present worksheet or your Private Macro Spreadsheet whenever you generate one. The Private Macro Spreadsheet is a collection of macros that you may access at any time, regardless of whatever worksheet is open.
However, because it is only accessible on your system, no one can access this using a different device.
If your worksheet has macro functionality but another user is unable to utilize it, it may be because your macros store in your own macro spreadsheet and are not available to them. If you need to share the worksheet with others, it’s crucial to remember to save the macros there as well.
Solution: Add New Trusted Location
We can add a new trusted location to solve this, for this to follow the procedures down.
STEPS:
- In the first place, go to the Developer tab from the ribbon.
- Then, under the Code category, go to Macro Security.
- Thus, you will be able to go to the Trust Center dialog box.
- Click on Trusted Locations and check mark Allow Trusted Locations on my network (not recommended).
- Subsequently, click on Add new location.
- The Microsoft Office Trusted Location window will appear.
- Now, click on Browse.
- Further, select the location and click OK.
- This will again take you to the Microsoft Office Trusted Location window.
- Checkmark the Subfolders of this location are also trusted.
- Then, click OK.
- Consequently, click OK on the Trust Center dialog.
Reason 4: Macro Functionality May be Hindered by Workbook Protection
Your spreadsheets may be “protected” in a variety of ways, as well as by preventing particular cells from being chosen or altered. Macros follow security, and if cells are shielded, they cannot be affected by macros. Macro functionality might not be available to some other users if you secured the worksheet while distributing it to them. Sometimes, you might see the warning.
Solution: Unblock Properties of Excel FileÂ
If we unlock the file, our macros will work perfectly. For this let’s look at the steps down.
STEPS:
- Right-click on the Excel file.
- Then, go to Properties.
- This will open the Properties dialog box.
- Now, check the mark on the Unlock box.
- Finally, click OK.
Reason 5: Other’s Excel File Content
While downloading from some site or other file, we may see the message after opening the Excel file. As Excel is a protected spreadsheet this will show the PROTECTED VIEW.
Solution: Enable All Content
To fix this, follow the instructions below.
STEPS:
- Go to the File tab.
- Click on the info option.
- Further, go to Protected View Settings.
- This will open the Trust Center dialog.
- From there, go to Protected View and check the mark as per your reference.
- Finally, click OK.
- Alternatively, click on Enable Editing from the Info option.
How to Fix Macro Settings Grayed Out in Excel?
In most cases, your domain administrators’ group policy settings may disable these choices; in that case, when you are hard-press to make changes without their consent. Try looking through your group policy settings if you are currently living alone. If this option is on, disable it. Not all of your Office programs had their macro settings altered.
STEPS:
- Click on the File tab.
- Choose Options.
- After selecting Trust Center, select Trust Center Settings.
- Press the Macro Settings button in the Trust Center.
- Make your desired options, then click OK.
Read More [Solved]: Macro Settings Greyed out in Excel
Things to Remember
- Unknown and untrusted sources present a security risk when using macros.
- It is necessary to export the VBA code file as a “macro-enabled workbook.” If we do not save the VBA code when we protect a file with a different extension, which prevents the macros from working.
- When “Enable all macros” is selected, all macros are executed without additional notification or permission. It could be risky because both trustworthy and untrusted sources’ macros are enabled.
Download Practice Workbook
You can download the workbook and practice with them.
Conclusion
The above methods will assist you in solving Excel files that are Macros-enabled but not working. I hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback.
