How to Run a Macro in Another Excel Workbook from a Custom Ribbon
Question details
The user wants to trigger a macro stored in a separate network .xlsm workbook via a custom ribbon button located in their PERSONAL.XLSB file.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to execute an external macro from a custom ribbon button mapped in a personal macro workbook.
- Observed behavior
- The Application.Run command fails with an error when triggered from the custom ribbon, even though the exact same command runs successfully from the VBA Immediate window.
Ensure that macros are enabled in your Trust Center settings and verify that the target workbook containing your macro is not corrupted.
Correct Application.Run Syntax and Workbook Status
Ensure the exact syntax is used for referencing external workbooks and that the target workbook is properly opened without naming conflicts.
When calling macros across different workbooks, Excel requires exact reference paths, particularly when spaces are involved in the workbook name. Additionally, the target workbook must be open, and there cannot be another workbook with the same name already open in the background.
In your VBA code, format the call using single quotes around the workbook name, followed by an exclamation mark and the macro name. Example: Application.Run "'Target Workbook.xlsm'!MacroName".
Add code to check if the target workbook is open. If it is closed, use Workbooks.Open to open the specific .xlsm file before the Application.Run command executes.
Excel cannot open two workbooks with the same name. Ensure no duplicate file is currently active or hidden in the background before running the macro.
Configure Trusted Locations for Network Workbooks
Macros stored on network drives may be blocked by default security protocols. Adding the network path to your Trusted Locations resolves execution failures.
Use WPS Office for Reliable Macro and VBA Support
If you frequently encounter ribbon and macro execution errors in Microsoft Excel, consider switching to WPS Office. It provides robust support for VBA macros and offers seamless compatibility with Excel formats in a lightweight package.
- 1. Download and install: Download WPS Office from the official website and complete the installation process.
- 2. Open your macro workbooks: Launch WPS Spreadsheet and seamlessly open your existing .xlsm or .xlsb files.
- 3. Access the Developer tab: Enable the Developer tab to manage your VBA code, run macros, and customize your workflow without execution errors.
Frequently Asked Questions
Why does Application.Run work in the Immediate window but not from the ribbon?
The Immediate window executes code in the context of your currently active VBA project. A custom ribbon button, especially one mapped to PERSONAL.XLSB, may lose this context or face stricter security barriers when attempting to call code in an external network file. Explicitly defining the workbook path and ensuring exact string syntax usually resolves this.
Can I run a macro from a closed workbook using Application.Run?
No, Excel requires the target workbook to be open before running a macro inside it. You must either open it manually beforehand or include VBA code (like Workbooks.Open) in your primary macro to open the target file before triggering the Application.Run command.
What causes the 'cannot open two workbooks with the same name' error?
Excel restricts users from opening two workbooks that share the same filename simultaneously, even if they are located in completely different folders or network drives. To fix this, you must locate and close the conflicting workbook before attempting to open the new one or run the macro.
