How to run VBA macro when open or close workbook?
In many cases we may use the VBA code to achieve the operations which Excel built-in functions not support. But have you ever tried to run the VBA macro in each time of opening or closing the workbook. In this article, I will tell you how to run the VBA code while opening or closing the workbook every time.
Run VBA code when close or open workbook
Run VBA code when close or open workbook
1. Enable the workbook, press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Double click ThisWorkbook in Project – VBAProject pane to open the ThisWorkbook (Code) window.
2. In the code window, select Workbook from the left drop down list. Notice that, in default, the Open will be displayed in right drop down list, if not, change it to Open.
3. Then copy your own code without the first and last line and paste between Private Sub Workbook_Open() and End Sub as below screenshot shown.
4. Then click Save button to save the code, in the popping reminder dialog, click No.
5 Then a Save As dialog pops out, save the file as Excel Macro-Enabled Workbook and select a folder to place the file.
6. Click Save button. Now the code will run while the workbook is open.
If you want to run the code while closing workbook each time, select Deactiviate from the right drop down list in the Code window, and copy the code you will run and paste between Private Sub Workbook_Deactivate() and End Sub.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!