他の Workbook のイベントを定義する

モーダルウインドウと、モードレスウインドウ

マクロを実行しているExcelファイルではなく、他のExcelファイルを操作するマクロと言うのは、よくある話ですよね？
そして、それにユーザーフォームを使用する事も、これまたよくある話です。
例えば 「ボタンを押下すると、指定のExcelファイルを開き、そのExcelファイルにデータ入力するための入力フォームが開かれる」 という場合です。
この時、データ入力するための入力フォームは、モーダルなウインドウの方が都合が良いです。
何故なら、入力データを記載するために開いたExcelファイルを閉じられると、面倒な事になるからです。

しかし、ツールを使用するユーザーは、入力フォーム表示中もExcelが操作したいと言います。
入力するためのデータの一部も、Excelに記載されているデータを参照して入力しているから、という事情らしいです。

そうなると、入力フォームをモードレスなウインドウで表示してやる必要が有ります。
ユーザーフォームをモードレスのウインドウとして表示させる場合、下記の様にvbModelessの引数を付けてShowメソッドを実行します。

ユーザーフォーム「UserForm1」をモードレスで表示する

UserForm1.Show vbModeless

フォームを表示した後もそのままコードが実行されるので、フォームを閉じた後に実行するコードは、Showメソッドの後に記載せずに、別に用意してやる必要が有ります。

モードレスにすると、他ブックのイベントが欲しくなる

他ブックのイベントを作ると、モードレスにしたくなる(菊〇宗)
冗談はさておき、入力フォームからワークブックに値を書き戻す際に、当該のワークブックが開かれたままかどうかを確認すればエラーは回避できますが、せっかく入力フォームで入力したのに、それを破棄するのはもったいない事です。
スマートに当該ブックを閉じられないようにしたい。
例えば、マクロを実行しているExcelファイル自身を閉じられないようにするなら、「ThisWorkbook」のBeforeCloseイベントを取得して、下記の様に閉じられないようにすれば簡単です。

マクロを実行しているExcelファイルを閉じられないようにする

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = False
End Sub

これを、他のブックでも行いたいのです。

WithEventsとは？

こういう時、WithEventsを使えば良いらしい。
私は今まで、WithEventsは、クラス内でEvent宣言した自作のイベントを適応する為のモノだと思い込んでしました。
しかし実際には、WithEvents宣言したオブジェクトが標準で持つイベントを、取得する事が出来ます。
例えば、下記の例ではワークブック変数objEventBookをWithEventsで宣言しているため、ワークブックオブジェクトがフック出来る_BeforePrint(Cancel As Boolean)や_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)等のイベントも、設定する事が出来ます。

Hook_BookCloseEvent_Class

Option Explicit
Private WithEvents objEventBook As Workbook

'監視対象のワークブックを設定
Public Property Set ExcelWorkbook(ByRef objWb As Workbook)
    Set objEventBook = objWb
End Property

'監視対象ワークブックのBeforeCloseイベント発生時に実行される
Private Sub objEventBook_BeforeClose(Cancel As Boolean)
    MsgBox "このブックは、現在【入力フォーム】で使用しております", vbOKOnly, "警告"
    Cancel = True
End Sub

'クラス生成時に初期化を行う
Private Sub Class_Initialize()
    Set objEventBook = Nothing
End Sub

'クラス破棄時に後始末を行う
Private Sub Class_Terminate()
    Set objEventBook = Nothing
End Sub

上記の様に記載したクラスオブジェクトに対して、利用する側のモジュールには、下記の様に記載します。

Hook_BookCloseEvent_Class を利用する側

'クラスオブジェクトの宣言
Public objBCloseClass As Hook_BookCloseEvent_Class

(中略、UserForm1を呼び出すプロシージャ内)
    'クラスオブジェクトを生成
    Set objBCloseClass = New Hook_BookCloseEvent_Class
    '監視対象ワークブックを設定
    Set objBCloseClass.ExcelWorkbook = Application.Workbooks("データリスト.xlsx")
    'フォームを表示
    UserForm1.Show vbModeless

(中略、UserForm1を閉じるプロシージャ内)
    '監視対象ワークブック設定を解除(クラスの後始末で実施されてるので、不要かもしれないけど念のため)
    Set objBCloseClass.ExcelWorkbook = Nothing
    'クラスの破棄
    Set objBCloseClass = Nothing
    'フォームを閉じる
    Unload UserForm1

とても便利です。
これを流用すれば、マクロなしの拡張子がxlsxのファイルに、あたかもイベントマクロが有る様に設定できるので、応用範囲は広そうです。