VBA Code: How to auto trigger operation on opening or closing an excel file

Problem : We need to perform or auto-trigger some operation while opening or closing an excel file. There may be operations required by excel user like connecting to database and auto populating the data from database  on opening the excel file and generating pivot tables and charts automatically based on database data. Similarly it can be any other event that user want to perform on opening excel file(saved in macro-enabled xlsm file format)

How to create a module in excel VBA?

Open the VBA editor by clicking Alt + F11 or go to Developer tab and click on Visual basic. Below image shows how to create a sub or function in VBA. Following are the steps for creating a sub or function in VBA:

1. Click on Developer

2. Click on Visual basic icon. (The above two steps can be achieved by clicking on Alt + F11.

3. In the workbook, add new module.

4. In the module, add sub as shown below.

How to create a sub that will be auto-triggered on opening the excel file?

Create a sub with name as auto_open as shown in the code below. Try writing a small code as shown below which will display a message box when the excel file opens.

 Sub auto_open()  
   MsgBox "Show message while opening the excel file"  
 End Sub  

How to create a sub that will be auto-triggered on closing the excel file?

Create a sub with name as auto_close as shown in the code below. Try writing a small code as shown below which will display a message box when the excel file closes.

 Sub auto_close()  
   MsgBox "Show message while closing the excel file"  
 End Sub  

Save the excel file in .xlsm format.