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.

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.

Select "Workbook" in the Object drop-down.

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.

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.

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

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.

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