ブックをオープンした時に発生するイベント「Open」と
クローズ時に発生するイベント「BeforeClose」で、
次のように処理します。

　　Visual Basic Editorを起動してプロジェクトエクスプローラの
「ThisWorkbook」をダブルクリックするとコード入力画面が出ます。
(General)となっているドロップダウン（＜オブジェクト＞ボックス）を
クリックし「Workbook」を選択すると自動的に

Private Sub Workbook_Open()

End Sub

　　と表示されるので、そこにオープン時に行う処理を記述します。

【例】
Private Sub Workbook_Open()

    MsgBox "ブックをオープンしました"

End Sub

　　クローズ時のイベントは、コード入力画面の「Open」となっているドロップ
　ダウン（＜プロシージャ＞ボックス）をクリックし「BeforeClose」を選択し、
　クローズ時に行う処理を記述します。

【例】
Private Sub Workbook_BeforeClose(Cancel As Boolean)

    MsgBox "ブックをクローズしました"

End Sub

このブックを一度保存して、もう一度開いたり、閉じたりしてみると、
メッセージボックスが表示されることが確認できます。