Reason 1 – Worksheet Might Be Saved as an .xlsx File
The file extension for spreadsheets that support macros is .xlsm, whereas that of unmodified worksheets is .xlsx.
If you shared a worksheet with another user that had macros enabled, then the macros stopped working later, they might’ve unintentionally re-saved the file as a regular workbook without macros.
Solution – Save the File with the .xlsm Extension
Steps:
- Go to the File tab from the ribbon.
- Click on Save As.
- Select Excel Macro-Enabled Workbook(*.xlsm) from the file extension drop-down menu.
- Click on the Save button to save your file as an .xlsm.
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 be harmed by macros.
Solution – Change Global Macro Settings
Steps:
- Go to the File tab from the ribbon.
- Select the Options menu.
- The Excel Options dialog box will appear.
- Go to the Trust Center option and click on Trust Center Settings…
- 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.
- Click on the OK button.
- This will again take you to the Excel Options dialog.
- Click OK again.
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 are stored in the private 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 a New Trusted Location
Steps:
- Go to the Developer tab from the ribbon.
- Under the Code category, go to Macro Security.
- You will get the Trust Center dialog box.
- Click on Trusted Locations and check Allow Trusted Locations on my network (not recommended).
- Click on Add new location.
- The Microsoft Office Trusted Location window will appear.
- Click on Browse.
- Select the location and click OK.
- This will again take you to the Microsoft Office Trusted Location window.
- Check Subfolders of this location are also trusted.
- Click OK.
- 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. Sometimes, you might see this warning to indicate that.
Solution – Unblock the Excel File
Steps:
- Right-click on the Excel file.
- Go to Properties.
- This will open the Properties dialog box.
- Check the mark on the Unlock box.
- Click OK.
Reason 5 – External Content
While downloading from some site or other file, you may see a message after opening the Excel file. This will bring up the PROTECTED VIEW which limits macro functionality.
Solution – Enable All Content
Steps:
- Go to the File tab.
- Click on the info option.
- Go to Protected View Settings.
- This will open the Trust Center dialog.
- Go to Protected View and check the mark as per your reference.
- Click OK.
- Click on Enable Editing from the Info option.
How to Fix Macro Settings Grayed Out in Excel
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 changes, 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.”
- 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 the Practice Workbook
