Workbook Level

Workbook level events are built-in and are available through its class module.
There are 40 different Excel > Workbook Level Events
Every workbook has a corresponding class module associated with it which is displayed in the Project Explorer window.
This can be seen under Microsoft Excel Objects.

alt text

Double click on the "ThisWorkbook" class module.
At the top of the code module there are two drop-down boxes.
The one on the left is called the Object drop-down.
The one of the right is called the Procedure drop-down.

alt text

Select "Workbook" in the Object drop-down.

alt text

When you select this object in the Object drop-down, the corresponding events are listed in the Procedure drop-down.
The Procedure drop-down box displays all the built-in workbook level events.

alt text

Selecting an event from the drop-down box will insert the corresponding event handler.
Select "BeforeClose" and select "NewSheet".
The reason you are also seeing the "Workbook_Open" event handler is because it is the default event handler.
When you select "Workbook" in the Object drop-down this event handler gets added automatically.

alt text

Keep the "Workbook_BeforeClose" and "Workbook_Open" event handlers and manually delete the other one.
Add the following code.

alt text

Save the workbook with type "Excel Macro-Enabled Workbook (*.xlsm)"
Save the workbook to the "C:\temp\" folder.
When you close the workbook you will see a message box pop up before it closes.

alt text

When you open the workbook another message box will pop up once the workbook is open.

alt text

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext