How to Fix Excel Macros Working Only After Reopening a Workbook
Question details
The user needs to fix an issue where Excel macros fail to run initially and only execute after the macro-enabled workbook is saved, closed, and reopened.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Running macros in a newly opened or downloaded macro-enabled workbook.
- Observed behavior
- Macros fail to run or are blocked by security settings initially, but work properly after the workbook is saved locally, closed, and reopened.
Ensure your macro-enabled workbook (.xlsm) is saved locally on your computer rather than opening it directly from an email attachment or an untrusted network drive.
Add the Workbook Folder to Trusted Locations
This is the most direct way to allow macros to run immediately without requiring a workbook restart.
By adding the folder where your workbook is saved to Excel's Trusted Locations, you bypass the Protected View and Macro security prompts. This allows the VBA scripts to execute immediately upon opening the file.
Open Microsoft Excel, click on 'File' in the top-left corner, and select 'Options' at the bottom of the left-hand pane.
In the Excel Options window, click on 'Trust Center' in the left menu, then click the 'Trust Center Settings' button.
Select 'Trusted Locations' from the left sidebar, click 'Add new location...', and browse to the specific folder where your workbook is saved.
Check the box for 'Subfolders of this location are also trusted' if necessary, click 'OK' to add the folder, and click 'OK' again to close the settings windows.
Use a Trusted Publisher or Code Signing
If distributing the workbook to others, signing the macro code ensures it runs seamlessly without users needing to change their Trust Center settings.
Try WPS Office for Seamless Macro Compatibility
If you are tired of dealing with complex Microsoft Excel security settings and macro blocks, consider trying WPS Office. It provides a lightweight, highly compatible alternative with built-in support for VBA and macros in its premium versions, offering a familiar interface without the cumbersome security hurdles.
- 1. Download and Install WPS Office: Visit the official WPS Office website to download the installer. Run the setup file to install the suite on your computer.
- 2. Open Your Macro-Enabled File: Launch WPS Spreadsheets, click 'Open', and select your .xlsm file containing the macros.
- 3. Enable Macros Easily: If prompted by the security warning at the top of the screen, click 'Enable Macros' to allow your VBA scripts to run instantly.
Frequently Asked Questions
Why do my Excel macros only work the second time I open the file?
When a file is downloaded from the internet or opened from an untrusted location, Excel initially opens it in Protected View, blocking macros. Saving the file locally and reopening it registers it as a trusted local file, bypassing the temporary security block.
Is it safe to add my Downloads folder to Trusted Locations?
No, adding your Downloads folder to Trusted Locations is highly discouraged. Because internet downloads are saved there by default, it exposes your computer to potentially harmful scripts from malicious files executing automatically.
How do I enable all macros in Excel temporarily?
You can enable all macros by going to File > Options > Trust Center > Trust Center Settings > Macro Settings and selecting 'Enable all macros'. However, this is not recommended for everyday use due to security risks.
Can I trust a document saved on a network drive?
Yes, but you need to explicitly allow trusted locations on your network. In Trust Center Settings under Trusted Locations, check the box for 'Allow Trusted Locations on my network' before attempting to add the network folder.
