Excelでどのシート、ブックにも共通の機能を追加したい

質問

Windows2000とXPでExcel2002を使っています。
次のような機能を追加したいと思い、マクロを考えています。
(機能)
シート(ブック)を閉じるときに特定の文字列を検索する。
文字列が発見されれば、ポップアップを表示する。
なければそのまま、セーブされる。(通常のクローズ処理)

シートごとにマクロを書いてしまえば楽ですが、ほかの人と共有する場合は邪魔になるので、自分のPC内だけの機能として実装したいと思います。

困っている点は以下の点です。
Personal.xlsにAuto_Close()を追加し、アドインを読み出す方法も考えましたが、Auto_Close()がExcel全体を閉じるとき(タイトルバーの×ボタンで閉じるとき)にしか呼ばれないため、Excel内のxボタンで閉じるときにはAuto_Close()の方法が使えません。

Excelでどのシート、ブックにも共通の機能を追加する場合にはどのようにするのがいいのでしょうか？

アドインを登録してボタンを追加する以外の方法を教えていただけるとうれしいです。
(元々チェックを忘れるのを避けるため、自動チェックにしたいと考えているからです。)

回答No.2

taocat · ベストアンサー

こんばんは。
やはりプログラム経験のある方だったんですねぇ。
質問を読みそう思いました。

Applicationオブジェクトは通常のオブジェクトとは違い、最初にちょっと準備がいります。

●●以下は全て、Personal.xls　のコードです●●

先ず、ＶＢＥ画面の「挿入」＞「クラスモジュール」からクラスモジュールを挿入し、そこに以下のコードを書いてください。

----------------------------------------------

Public WithEvents App As Application

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)

　If Wb.Name <> "Personal.xls" Then

　　▲ここにブックの共通処理コード▲

　End If

End Sub
-------------------------------------------------

それから、標準モジュールを挿入し以下のコードを書いてください。

-------------------------------------------------

Dim PersonalApp As New Class1

Sub Auto_Open()

　　Set PersonalApp.App = Application

End Sub

------------------------------------------------

Personal.xlsのAuto_Openで
Set PersonalApp.App = Application
を実行することによりApplicationオブジェクトが使えるようになります。
そしてApplicationにイベントが発生すると、クラスモジュールのイベントが実行されます。

尚、詳しくはＶＢＡのヘルプをご覧ください。
以上です。

お礼

わかりやすい回答ありがとうございます。
早速やってみて動きました。

イベント部分だけ変えれば汎用的につかえそうな、よい方法で勉強になりました。あとはVBAのヘルプで何とかなりそうです。

たいへん助かりました。

回答No.1

taocat

こんばんは。

Personal.xlsの方で
Applicationオブジェクトの
WorkbookBeforeCloseイベントを使ってみて下さい。

補足

回答ありがとうございます。

いただいたヒントを手がかりにいじってみたのですが、自分の方法があっているか、そもそもヒントもすべて理解できませんでした。

ApplicationのWorkbookBeforeCloseイベントプロシージャーにひっかければOK、と理解しているのですが具体的なコード(というか、Excel VBAでの書き方)が思いつきません。

もしよろしければ、もう少し具体的なコードまで教えていただけないでしょうか。
(プログラムの経験はあるのでマクロ、処理記述はできますが、Excelのオブジェクトやマクロをどこに書くのか、といったお作法がわかりません。)