Щёлкни правой кнопкой на ярлычке листа - Исходный текст - сюда вставляются обработки событий листа...

WorkbookBeforeClose Event
See AlsoApplies ToExampleSpecificsOccurs immediately before any open workbook closes.

Private Sub object_WorkbookBeforeClose(ByVal Wb As Workbook, ByVal Cancel As Boolean)
object An object of type Application declared with events in a class module. For more information, see Using Events with the Application Object.

Wb The workbook that's being closed.

Cancel False when the event occurs. If the event procedure sets this argument to True, the workbook doesn't close when the procedure is finished.

Example
This example prompts the user for a yes or no response before closing any workbook.

А чтобы это юзать:

Using Events with the Application Object
See AlsoSpecificsBefore you can use events with the Application object, you must create a new class module and declare an object of type Application with events. For example, assume that a new class module is created and called EventClassModule. The new class module contains the following code.

Public WithEvents App As Application

After the new object has been declared with events, it appears in the Object drop-down list box in the class module, and you can write event procedures for the new object. (When you select the new object in the Object box, the valid events for that object are listed in the Procedure drop-down list box.)

Before the procedures will run, however, you must connect the declared object in the class module with the Application object. You can do this with the following code from any module.


After you run the InitializeApp procedure, the App object in the class module points to the Microsoft Excel Application object, and the event procedures in the class module will run when the events occur.