エクセルでブックを閉じたときマクロを終了させるには

質問

現在、以下のようなマクロを組んでいます。

---------------------------------------
Dim 利用制限時間 As Integer

Private Sub Workbook_Open()

If Not ThisWorkbook.ReadOnly Then
利用制限時間 = 10 '分 + 起動時刻:Now
警告時刻 = Now + 10 * TimeValue("00:01:00") '分に変換
Application.OnTime 警告時刻, "ThisWorkbook.利用制限ご注意"
End If

End Sub

Private Sub 利用制限ご注意()
警告文 = "共有ファイルを開いて" + CStr(利用制限時間) + "分経過しました。" + vbCrLf
警告文 = 警告文 + "使用しない場合は終了してください。"
MsgBox 警告文, vbCritical, "共有ファイルの利用について"

Workbooks("共有ファイル.xls").Close

End Sub

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

しかし、10分たたずにブックのみ終了し、エクセルのみの起動をしていても10分後には、「共有ファイルを開いて10分経過しました」と出てきます。
エクセルを終了させればよいのでしょうが、ブックを終了させただけでマクロも同時に終了できないでしょうか？

回答No.9

asobe · ベストアンサー

こんにちわ。

#7の回答では時間が無くて良く見れなかったのですが、気づいた点を１つ。
警告分では「使用しない場合は終了してください。」と出力しているのですから、
利用継続の有無を確認するべきだと思います。

それらを踏まえてサンプルを乗せます。
そろそろ完成出来るといいですね。

Private 開始時刻 As Date
Private 警告時刻 As Date
'利用制限時間については定数で宣言するよりも、
'設定専用シートを用意し読み込むか、
'又はIniファイルなどから読み込むようにすると
'汎用性を持たせることが出来るでしょう。
Private Const 利用制限時間 As Integer = 10 '分

Private Sub Workbook_Open()
開始時刻 = Now
Call SetOnTime
End Sub

Private Sub 利用制限ご注意()
Dim 警告文 As String
警告文 = vbNullString
警告文 = 警告文 & ThisWorkbook.Name & "を開いて" & CStr(DateDiff("n", 開始時刻, 警告時刻)) & "分経過しました。" & vbCrLf
警告文 = 警告文 & "使用しない場合は終了してください。" & vbCrLf
警告文 = 警告文 & "継続して使用しますか？"
If MsgBox(警告文, vbYesNo Or vbExclamation, "共有ファイルの利用について") = vbYes Then
Call SetOnTime
Exit Sub
End If
ThisWorkbook.Close
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Now < 警告時刻 Then Call ResetOntime
If ThisWorkbook.Saved = False Then
If MsgBox("'" & ThisWorkbook.Name & "'への変更を保存しますか？", _
vbYesNo Or vbExclamation, "Microsoft Excel") = vbOK Then
ThisWorkbook.Save
Else
ThisWorkbook.Saved = True
ThisWorkbook.Close
End If
End If
End Sub

Private Sub SetOnTime()
If Not ThisWorkbook.ReadOnly Then
警告時刻 = DateAdd("n", 利用制限時間, Now) '現在時刻＋利用制限時間
Application.OnTime 警告時刻, "ThisWorkbook.利用制限ご注意"
End If
End Sub

Private Sub ResetOntime()
Application.OnTime 警告時刻, "ThisWorkbook.利用制限ご注意", Schedule:=False
End Sub

Wendy02さんへ。
もしお気を悪くされていたらごめんなさい。
好みの問題もありますから、人それぞれで良いと思います。
出すぎた真似をしてすみませんでした。

お礼

回答ありがとうございます。無事解決いたしました。wendy02さんやasobeさんにはご迷惑をおかけしました。

回答No.8

Wendy02

こんにちは。

昨日から、バカみたいなミスを連発してしまいました。すみません。

　If MsgBox("保存しますか？", vbYesNo) = vbOK
　　↓
　If MsgBox("保存しますか？", vbYesNo) = vbYes Then

asobeさんのおしっしゃる後の件は、全角文字については、そのとおりですが、これについて、そのとおりです。

そのほかについては、よく、そうExcel VBAのユーザー以外の方はそう理解されているようですが、ミスを連発した私が言っていると、何にもならないかもしれませんが、Excel VBAのコードを書く私としては、標準モジュール以外のキーワードについては、「明示的」「暗黙的」について、肯定否定をしません。

私は、Excel VBAのコードに多く接していますが、あくまでも、Excelの「専門家」などという立場ではありません。また、VBAを職業にして専門にしている方などは、モーグの『プロジェクトA』ぐらいだけで、めったにいないと思います。ただ、非VBAの専門家＝VB系のプログラマの方がExcelのVBAを書く時に、私には奇妙に映ることがあります。

それは、VBのルールをそのまま適用しているようですが、Excel VBAは、Visual Basic ではありません。あくまでも、スクリプト言語でしかありません。

恥の上塗りかもしれませんが、Excel VBAでも、Class に書くなら別ですが、おっしゃるようなことは、Excel VBAの一般的な書法スタイルには経験的？にないと思います。（もちろん、反論されても話の落とし所なんて、もともとないのですが、私はそう思っています。）

明示的に書くことについて悪いことではないのですが、Excel VBAでは、標準モジュール以外のオブジェクトモジュールには、明示的にしない限りは、一般的にモジュールレベルしかスコープしかないので、文字のムダになってしまいます。だから、特別な理由がない限りは、Privateステートメントをつける例は、ほとんど見かけないということです。

逆にいうと、標準モジュールで、Public と付けるのも奇妙なのですね。

回答No.7

asobe

今、ちょっと時間が無いので、確認はしていませんが、
以下のところが間違っている為ではありませんか？
>If MsgBox("保存しますか？", vbYesNo) = vbOK Then
vbOK ではなく vbYes ですね。
これで解決しない場合はまたご連絡ください。

アドバイスになりますが、メッセージボックスにアイコンも表示すると良いでしょう。
この場合は以下のどちらかですかね。
MsgBox("保存しますか？", vbYesNo Or vbInformation)
MsgBox("保存しますか？", vbYesNo Or vbExclamation)

さらに欲をいうなら、モジュールレベルでの変数宣言はDimは避け、Privateを用いたほうが良いでしょう。明示的に宣言出来ます。
Constも必ずPrivate Constと記述するべきです。
また、いまさらですが変数名も本来は全角文字は避けるべきですね。

回答No.6

Wendy02

こんばんは。Wendy02です。

同じブックだったのですね。(笑
それじゃ、私のコードがヘンですね。ご質問者には、いろんな人がいましてね。（^^;

＞ThisWorkbook.Close にしなかった理由はわかりませんが、、、

違うのだなって思った次第です。だから、ぜんぜん、勘違いも良いところです。別のブックからの制御を考えていたからです。一応、これで、名誉挽回になるんでしょうかしらね。

＞10分後の指示を「OK」したあと、保存するかしないかを聞いてきてほしいんです。

利用制限時間は、定数にして上に出しました。

'<ThisWorkbookモジュール>
Dim 警告時刻 As Date
Const 利用制限時間 As Integer = 1 '分 + 起動時刻:Now
Private Sub Workbook_Open()
If Not ThisWorkbook.ReadOnly Then
　　警告時刻 = Now + 利用制限時間 * TimeValue("00:01:00") '分に変換
　　Application.OnTime 警告時刻, "ThisWorkbook.利用制限ご注意"
End If
End Sub

Private Sub 利用制限ご注意()
　 警告文 = "共有ファイルを開いて" & CStr(利用制限時間) & "分経過しました。" & vbCrLf
　 警告文 = 警告文 & "使用しない場合は終了してください。"
　 MsgBox 警告文, vbCritical, "共有ファイルの利用について"
　 ThisWorkbook.Close
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
　 On Error Resume Next
　 Application.OnTime 警告時刻, "ThisWorkbook.利用制限ご注意", Schedule:=False
　 If ThisWorkbook.Saved = False Then
　　　If MsgBox("保存しますか？", vbYesNo) = vbOK Then
　　　　 ThisWorkbook.Save
　　　Else
　　　　 ThisWorkbook.Saved = True
　　　　 ThisWorkbook.Close
　　　End If
　 End If
End Sub

なお、保存しますか？が聞いてこなければ、保存する必要はありません。

補足

回答ありがとうございます。ほぼ動作OKだと思います。
ただ、「保存しますか？」で「はい」にしても、内容が保存されません。ほんとに初心者の発言で申し訳ありません。

回答No.5

asobe

#2の回答に示したBeforeCloseイベントでは、
解決出来なかったということでしょうか？

利用制限時間という変数の使用に関するアドバイスの方ではなくて、その上に示したサンプルはお試しになりましたか？

警告時刻という変数はモジュールレベルでの宣言に変更してくださいね。

補足

回答ありがとうございます。
10分後の指示を「OK」したあと、保存するかしないかを聞いてきてほしいんです。今のままだと強制終了になってしまって…お手数ですが手ほどきよろしくおねがいします。

回答No.4

asobe

現状の問題点は、Excelアプリケーションを上げたままだと、
"共有ファイル.xls"と言う名前のBookをすでに閉じているのに、
警告時刻にマクロが起動し、警告メッセージが出力されてしまうところですよね？

Wendy02さんへ
> 元のコードをみて、OnTimeメソッドを設定する側と共有ファイル側とは別のブックのようですから
なぜそう思うのですか？
Workbooks("共有ファイル.xls").Close だからですか？
私が思うにJJJJJJJJJさんは、
・LAN上などでサーバなどにおいているBookを開きっぱなしにしている人に警告を出したい
というような目的でマクロを組んだように思えるのですが、
だとすれば、OnTimeを設定する側と共有ファイル側とは同じBookのように思えます。
ThisWorkbook.Close にしなかった理由はわかりませんが、、、
私の思い込みだったらすみません…

> もし、自ブックなら、
> 　　ThisWorbook.Close False
> とでもすればよいはずですからね。
Closeメソッドの引数にFalseを渡しても、アプリケーションを閉じない限り、OnTimeは生きているはずです。

よってCloseイベントでOnTimeを解除しました。

ただし、Wendy02さんがおっしゃるように、一方的に閉じるのではなく
変更点があった場合、保存を促す処理は必須だと思います。

お礼

>現状の問題点は、Excelアプリケーションを上げたままだと、"共有ファイル.xls"と言う名前のBookをすでに閉じているのに、警告時刻にマクロが起動し、警告メッセージが出力されてしまうところですよね？
>私が思うにJJJJJJJJJさんは、LAN上などでサーバなどにおいているBookを開きっぱなしにしている人に警告を出したいというような目的でマクロを組んだように思えるのですが…

その通りなんです。LANを経由して複数の人がアクセスするファイルを開きっぱなしにしている人への警告をだしたいのです。あと少しで解決しそうなのですが…よろしくお願いします。

回答No.3

Wendy02

こんにちは。

ちょっと失礼させていただきます。

#2 さんの方法は思いつきましたが、直感的に面倒だと感じました。OnTimeの解除の方法というのは、私も使いますが、ヘルプのようにはいかないのですね。

元のコードをみて、OnTimeメソッドを設定する側と共有ファイル側とは別のブックのようですから、OnTimeメソッドの設定した以外のブックから、BeforeCloseイベントで、終了させるには、できないことはないけれど、ちょっと複雑な構成になってしまいます。

もし、自ブックなら、
　　ThisWorbook.Close False
とでもすればよいはずですからね。

一応、参考までに書いておきますが、たぶん、こんなふうになるのだと思っています。

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

手順:

最初に、共有ファイル側から、元ファイルに参照設定をしておきます。

＊注意:参照設定される側のプロジェクト名は、ユニークにしておかないと、設定できないはずです。

元ファイルの標準モジュールに以下のようにして、Public 変数にして置きます。
'--------------------------
Public 警告時刻　As Date
Public myBook As String

'--------------------------

'元ファイルのThisWorkbook
'--------------------------
Dim 利用制限時間 As Integer

Private Sub Workbook_Open()
If Not ThisWorkbook.ReadOnly Then
myBook = ThisWorkbook.Name
利用制限時間 = 10 '分 + 起動時刻:Now
警告時刻 = Now + 利用制限時間 * TimeValue("00:01:00") '分に変換
Application.OnTime 警告時刻, "ThisWorkbook.利用制限ご注意"
End If

End Sub

Private Sub 利用制限ご注意()
　　警告文 = "共有ファイルを開いて" + CStr(利用制限時間) + "分経過しました。" + vbCrLf
　　警告文 = 警告文 + "使用しない場合は終了してください。"
　　MsgBox 警告文, vbCritical, "共有ファイルの利用について"
　　Workbooks("共有ファイル.xls").Close False
End Sub

'-----------------------------
'共有側のThisWorkbookに、

Private Sub Workbook_BeforeClose(Cancel As Boolean)
　Application.OnTime EarliestTime:=警告時刻, _
　Procedure:=myBook & "!ThisWorkbook.利用制限ご注意", Schedule:=False
End Sub

'------------------------------
私の考え方や解釈に間違いがあるなら、この限りではありませんが、かなり面倒に感じますね。

回答No.2

asobe

OnTimeのヘルプはご覧になりましたか？
自動実行の解除の方法が記載されています。
サンプルはこんな感じでしょうか。

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime EarliestTime:=警告時刻, _
Procedure:="ThisWorkbook.利用制限ご注意", Schedule:=False
End Sub

ちなみに、Openイベント内の
>警告時刻 = Now + 10 * TimeValue("00:01:00") '分に変換
は、以下の間違いではないですか？
警告時刻 = Now + 利用制限時間 * TimeValue("00:01:00")

お礼

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

>ちなみに、Openイベント内の警告時刻 = Now + 10 * TimeValue("00:01:00") '分に変換は、以下の間違いではないですか？警告時刻 = Now + 利用制限時間 * TimeValue("00:01:00")

この方法を用いれば、時間を変えたいとき、1箇所の変更で済むんですね。 ためになりました。ありがとうございます。

本題のマクロのほうですが、やはりエクセルのみを起動した状態でブックと同時にマクロを終了させることは出来ないのでしょうか？

回答No.1

Wendy02

こんにちは。

以下のようにすればよいのでは？
ところで、私は、

Workbooks("共有ファイル.xls").Close False

としましたが、変更があった時に、強制的に保存するか、保存しないか、マクロで指定しておかないといけないのではありませんか？

'----------------------------------------------------
Private Sub 利用制限ご注意()
Dim wb As Variant
Dim flg As Boolean
For Each wb In Workbooks
　If wb.Name = "共有ファイル.xls" Then flg = True
Next wb
If flg Then
　　警告文 = "共有ファイルを開いて" + CStr(利用制限時間) + "分経過しました。" + vbCrLf
　　警告文 = 警告文 + "使用しない場合は終了してください。"
　　MsgBox 警告文, vbCritical, "共有ファイルの利用について"

　　Workbooks("共有ファイル.xls").Close False
End If

End Sub
'-----------------------------------------

補足

回答ありがとうございます。
この方法を試したのですが、やはりブックのみを終了させ、エクセル自体を起動させていると、「セキュリティ警告」が表示され、再度共有ファイルが起動されてしまいます。

>ところで、私は、Workbooks("共有ファイル.xls").Close Falseとしましたが、変更があった時に、強制的に保存するか、保存しないか、マクロで指定しておかないといけないのではありませんか？

そうですね。確かに保存の有無を指定するマクロを組む必要があるかもしれませんね。ご指摘ありがとうございます。