How to Automatically Run an Excel Macro When Any Workbook Opens
Question details
The user needs a way to trigger an Excel macro automatically every time any workbook (XLSX or XLSM) is opened, similar to the AutoOpen behavior in Word.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Automating repetitive setup tasks or running background scripts automatically upon opening any spreadsheet.
- Observed behavior
- The user understands how to use Normal.dotm for AutoOpen in Word but is unsure how to achieve the equivalent application-wide automatic execution behavior in Excel.
Ensure that the Developer tab is enabled in your spreadsheet application and that your Macro Security settings allow VBA scripts to run.
Use Personal.xlsb and Application-Level Events
By combining your Personal Macro Workbook (Personal.xlsb) with an Application-level event handler, you can detect when any workbook is opened and execute your VBA code accordingly.
Unlike Microsoft Word, Excel does not have a single Normal template that forces an AutoOpen macro on every file. Instead, you must capture the Application's WorkbookOpen event. By placing this code in the Personal Macro Workbook, it loads silently in the background every time Excel starts.
If you don't already have one, go to the View tab, click Macros > Record Macro. Choose 'Personal Macro Workbook' from the 'Store macro in' dropdown, click OK, and then immediately click Stop Recording. This creates the Personal.xlsb file.
Press Alt + F11 to open the Visual Basic for Applications (VBA) editor. In the Project Explorer pane on the left, locate 'VBAProject (PERSONAL.XLSB)'.
Right-click 'VBAProject (PERSONAL.XLSB)', select Insert > Class Module. In the Properties pane, rename this class module to 'AppEventClass'. In the code window, type: Public WithEvents App As Application
At the top of the AppEventClass code window, select 'App' from the left dropdown menu and 'WorkbookOpen' from the right dropdown menu. Inside the 'Private Sub App_WorkbookOpen(ByVal Wb As Workbook)' procedure that appears, paste the macro code you want to run automatically.
In the Project Explorer, double-click 'ThisWorkbook' under PERSONAL.XLSB. Add the following code to instantiate the class: 'Dim myApp As New AppEventClass' at the top, and then inside 'Private Sub Workbook_Open()', add 'Set myApp.App = Application'.
Click the Save icon in the VBA editor to save PERSONAL.XLSB. Close the editor and restart Excel. Your macro will now trigger whenever any workbook is opened.
Automate Your Workflow with WPS Spreadsheets VBA
WPS Office offers comprehensive support for VBA macros, allowing you to run Application-level events, manage Personal Macro Workbooks, and automate repetitive tasks just like you would in Microsoft Excel.
- 1. Install WPS Office: Download and install the latest version of WPS Office on your computer.
- 2. Enable the Developer Tab: Open WPS Spreadsheets, go to Options, and ensure the Developer tab is enabled to access macro tools.
- 3. Open the VBA Editor: Click the Developer tab and select 'VBA Editor' to access the robust scripting environment.
- 4. Deploy Your Code: Paste your existing Excel VBA code directly into the modules to achieve cross-workbook automation seamlessly.
Frequently Asked Questions
Why is my macro not running when I open a new workbook?
Your macro security settings might be restricting execution. Go to your Trust Center settings and ensure that macros are enabled. Additionally, check that your Personal.xlsb file is correctly located in the application's XLSTART folder.
Can I use an Excel Add-in (.xlam) instead of Personal.xlsb?
Yes. You can save your VBA code and application-level event handlers as an Excel Add-in (.xlam). Once you activate the Add-in via the Developer tab, the code will run in the background and detect when any workbook is opened.
How do I trigger a macro only for one specific workbook?
If you only want the macro to run for a single workbook, do not use the Application-level event. Instead, open the VBA editor, double-click 'ThisWorkbook' for that specific file, and place your code inside the 'Private Sub Workbook_Open()' procedure.
