エクセル ブック（マクロを含む）を保存してとじると、また開きます。

質問

ブックに保護をかけた状態で保存してあります。
共有状態にあり、マクロを有効にしてもらいたいので、
(1)開くときに保護解除
(2)保存のときに、保護をかけて保存後、（まだ閉じるとは限らないので）保護解除
ということをやろうとし、以下のように書きました。

エクセルごとではなく、このファイルのみ終了しようとすると、
開いた後と上書保存後に保護を解除する為、
ファイルを終了する時には必ず「変更を保存しますか？」と訊かれ、
その時に「はい」を選択すると、一度はそのファイルが終了するのですが、
すぐに マクロを含むファイルを開くときに出るメッセージ「セキュリティ警告マクロ　マクロ有効　マクロ無効　詳細」が出て、また開こうとします。
保存「いいえ」を選択すると、普通に終了します。
エクセルごとを終了すれば保存「はい」にしても、普通に終了します。

このファイルのみ終了する時、保存の確認で、「はい」を選び、普通に終了するには、何を直したらよいでしょうか？
その前に、根本的に間違ってたりしますでしょうか・・・。
よろしくお願いします。

ThisWorkbookに、
Private Sub Workbook_Open()
ActiveSheet.Unprotect Password:="（パスワード）"
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

ActiveSheet.Protect Password:="（パスワード）"
Application.OnTime Now, "保護解除"
End Sub

標準モジュールに、
Sub 保護解除()
ActiveSheet.Unprotect Password:="（パスワード）"
End Sub

回答No.8

Wendy02 · ベストアンサー

chihuma　さん、merlionXX さん、こんばんは。

> 砂時計のまま、画面がもどってこないですか？

少し、見させていただきました。どうやら、命令が二重になって、不履行のコマンドが残っているようです。

[Ctrl + F4] = Window Close でも、基本的には同じ考え方のはずですが、やはりちょっと、命令の実行にはズレがあるのかもしれませんね。たぶん、こういうことで解消するのではないかと思います。

------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
　　If ThisWorkbook.Saved = False Then　'←変更したけれど保存されていない場合
　　　　If MsgBox("保存しますか？", vbYesNo + vbQuestion, "（＾o＾）？　") = vbYes Then
　　　　　ThisWorkbook.Save
　　　　Else
　　　　 ThisWorkbook.Saved = True　
　　　　End If
　　End If
　　'ThisWorkbook.Close　'←二重の命令(不要）
End Sub
------------------
これは、すでに、Close の命令が出ているので、その上に、ThisWorkbook.Close をする必要がありません。

それと、OnTime(EarliestTime, Procedure, LatestTime, Schedule)

のLatestTime に以下を入れるというのは、
myTime + TimeSerial(0, 0, 1)

命令不履行になったときには、経過時間が過ぎたら待たないという意味なのです。それを入れないと、いつまでも、命令の履行を待ってしまうわけですね。実際に、そのような現象があるのか、はっきりはしていないのですが。

------------------
myTime = Now + TimeSerial(0, 0, 1) 'そのままNow だけでも可、でも今回は関係ないと思いますが、変数で置く習慣を身に着けたほうがよいと思います。　　
　　Application.OnTime myTime, "保護解除", myTime + TimeSerial(0, 0, 1)
それから、Sub 保護解除() 側の最後に、以下を入れたほうがよいですね。
　　ThisWorkbook.Saved = True　'←これは、「保護解除」で変更されたけれども、それはカウントしないという意味です。そうすると、ThisWorkbook.Saved は、純粋に、これ以外の変更だけを監視できます。

また、私の#5のマクロに書かれていませんでしたが、 Workbook_Open()　は、Call 保護解除を呼び出すか、UnProtect した後に、Saved = True を入れないと、開いただけでも変更のフラグが立ってしまいます。
------------------

なお、私の場合は、標準モジュールに行き来するタイムラグと、オートメーション・オブジェクト(=CreateObject)の場合に備えて、
"ThisWorkbook.保護解除"

としました。オートメーション・オブジェクトにすると、標準モジュールが素通りしてしまいます。本当は、オートメーションにしたら、セキュリティのために、逆に、プロテクトのままにするという方法も良いかもしれません。私は、最初、マクロの意味が、よく分からなかったけれども、これは、セキュリティとマクロを有効にするために考えられたもので、単純だけどアイデアとしては、抜群だと思います。私もメモに残して、今後の参考にさせていただくことにします。

お礼

回答ありがとうございます<(_ _)><(_ _)><(_ _)><(_ _)>

丁寧な解説＆アドバイスありがとうございました！

回答No.7

merlionXX

> 砂時計のまま、画面がもどってこないですか？

ほんとですね！気が付かなかった。
質問についてはWendy02さんの回答で解決したと思いますが、この現象はなぜなんだろう・・・・。不思議です。

お礼

回答ありがとうございます<(_ _)><(_ _)>

なぜなんでしょう；

回答No.6

Wendy02

#5 の文章の訂正です。

読み返してみたら、文章がヘンでした。（^^;

×ご質問者さん以上に、それだけの知恵がある人が社内に何人いるか、と数えたほうが早いような気がしますね。

○ご質問者さん以上に、それだけ、その設定に詳しい人が社内には何人もいないのではないでしょうか。

という意味です。変な書き方で、すみません。

回答No.5

Wendy02

こんにちは。

少し割り込みさせていただきます。

ご質問者さん以上に、それだけの知恵がある人が社内に何人いるか、と数えたほうが早いような気がしますね。あまり、回答者の対応に、現象だけで反論しても、プログラムとしての論理をご自身で追ってみたほうが良いような気がします。

>(1)開くときに保護解除
>(2)保存のときに、保護をかけて保存後、（まだ閉じるとは限らないので）保護解除

元のマクロの趣旨というのは、マクロを使わないで開けると、プロテクトの状態になっているようにするということではないでしょうか。それなら、保存するときにのみ、プロテクトを掛けるということだけを念頭に置けばよいと思います。

元のコードの問題は、OnTime　メソッド　が、あまりうまく働いていないようです。OnTimeメソッドが生きているので、終了しても、戻ってくるわけです。

それで、「保護解除」は、タイムラグやオートメーションの問題が気になるので、ThisWorkbook 側に置くことにします。

なお、挙動自体が、気になるなら、以下のブロックアウトを外して調べてみてください。
　ActiveSheet.Range("A1").Value = "unP"'　/"P" 'unProtect/Protect

'ThisWorkbook　モジュール
'--------------------------------------

Private Sub Workbook_BeforeClose(Cancel As Boolean)
　If ThisWorkbook.Saved = False Then
　 If MsgBox("保存されていませんが、そのまま終了しますか？" & vbCrLf & _
　　　"[キャンセル->保存]", vbOKCancel + vbInformation) = vbCancel Then
　　　 Cancel = True
　 Else
　　ThisWorkbook.Saved = True
　 End If
　End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myTime As Date
'ActiveSheet.Range("A1").Value = "P"
　ActiveSheet.Protect Password:="パスワード"
　myTime = Now + TimeSerial(0, 0, 1)
　Application.OnTime myTime, "ThisWorkbook.保護解除", myTime + TimeSerial(0, 0, 1)
End Sub

Private Sub Workbook_Open()
　ActiveSheet.Unprotect Password:="パスワード"
　'ActiveSheet.Range("A1").Value = "unP"
End Sub

Sub 保護解除()
ActiveSheet.Unprotect Password:="パスワード"
'ActiveSheet.Range("A1").Value = "unP"
ThisWorkbook.Saved = True
End Sub

お礼

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

>OnTimeメソッドが生きているので、終了しても、戻ってくるわけです。

あーーー…；

BeforeCloseのところ、なるほどなぁと思いました。
また、保護状態確認もなるほどなぁと思いました。
ありがとうございました。

回答No.4

merlionXX

#1.2.3です。
これではいかがでしょう？（#2での回答は、一旦他のセルを選択しないと保護解除されないため修正しました。）

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Static st As Integer
Dim rtn As Integer
st = st + 1
If st > 1 Then Exit Sub
rtn = MsgBox("保存しますか？", vbYesNo + vbQuestion, "（＾o＾）？　")
If rtn = vbYes Then
ThisWorkbook.Save
End If
ThisWorkbook.Close (False)
End Sub

Private Sub Workbook_Open()
ActiveSheet.Unprotect Password:="パスワード"
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveSheet.Protect Password:="パスワード"
Application.OnTime Now, "保護解除"
End Sub

お礼

回答感謝です！

そうしますと、
他のファイルも開いていた場合、
何も入力せずに終了Ctrl+F4して、保存をいいえにすると、
砂時計のまま、画面がもどってこないですか？

・・・うわ、質問攻めですね；すみません。

回答No.3

merlionXX

難しいですねえ。
これでは？

Private Sub Workbook_Open()
ActiveSheet.Unprotect Password:="パスワード"
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveSheet.Protect Password:="パスワード"
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Call 保護解除
End Sub

補足

回答ありがとうございます<(_ _)>

Private Sub Workbook_SheetSelectionChangeということは、
シートが変わったら保護解除するよ、ということでよろしかたったですか？

そうすると、
上書保存Ctrl+s（ファイルを閉じない）すると、シートは変わって無いので、保護解除が呼び出されないですか？

ちょっと保護解除から離れまして、
上書保存した後、必ずファイルが閉じるということはできたりしますか？？
その動きができれば、充分うれしかったりします。

回答No.2

merlionXX

#1です。
たしかに途中で上書き保存されたらそうなりますね。

では、こうしたら？
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveSheet.Protect Password:="パスワード"
ActiveWorkbook.Save’保存
End Sub

補足

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

そうしますと、閉じる時、必ず保存されますか？
一度上書保存した後、何か入力し、それを保存したくない場合でも保存されますか？
ファイルを共有していますので、そんな状況もないとは言えません…。

BeforeSaveのところで、保護して保存した後、
（このファイル名）.xlsというファイルが開いていれば、保存の後に"保護解除"。なければ、exit。
のような感じは出来るのでしょうか？
できたとしても、Crtl+F4を押し、保存を「はい」にした場合、一度終了してすぐにもう一度そのファイルを開こうとするメッセージが出る気がするのですが…。

回答No.1

merlionXX

もともとのファイルでシートを保護しておけば
Private Sub Workbook_BeforeSave ではなくて

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveSheet.Protect Password:="パスワード"
End Sub

でいいんじゃないですか？

補足

回答ありがとうございます！
そうすると、例えば、
(1)セルに何か入力。
(2)Ctrl+sで上書保存。
(3)セルに何か入力。
(4)Ctrl+F4で(3)の変更を保存せずにブックを閉じる。
とすると、次に開こうとする場合、マクロを無効にしても、保護が解除されていませんか？

すみません；
とんちんかんちんなことを言っているかもしれません；