[[20211210221919]]　『マクロを有効にして操作させる』（フォーキー）　ページの最後に飛ぶ

『マクロを有効にして操作させる』（フォーキー）

お世話になります。
マクロ有効化で操作させたいファイルがあり、

１、マクロが無効だとシート保護状態
２、マクロ有効でシート保護解除
３、閉じる前にシート保護＆保存して閉じる

という手順をとっています。

３の処理に、

保存しない場合もシートは保護する。

を追加するにはどうすればいいですか？

シート保護を有効にするには保存して閉じなければならないので、現在の手順では無理な気がします。
解決方法があれば、ご教授お願いします。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

 保存しなければ、次開くときはシート保護がかかったままなのでは？
 ちがうのかな？
（´・ω・`） 2021/12/10(金) 22:50

途中で上書き保存をしたりしているなら、Beforesaveイベントで
Cancel = Trueしてから
シート保護
↓
上書き保存
↓
シート保護解除
してみるとか。
手品みたいなやり方だけど保存前に一度保護するから開くときはいつも保護された状態。
（きまぐれおじさん） 2021/12/10(金) 23:08

＞保存しなければ、次開くときはシート保護がかかったままなのでは？

（´・ω・`）さん、きまぐれおじさんのおっしゃる通り、途中で上書き保存することを想定した質問でした。言葉足らずで申し訳ありません。

きまぐれおじさん、

 Private Sub Workbook_BeforeClose(Cancel As Boolean)
     Worksheets("Sheet1").Protect

 End Sub

 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
     Cancel = True
     With Worksheets("Sheet1")
         .Protect
         ThisWorkbook.Save
         .Unprotect
     End With
 End Sub

 Private Sub Workbook_Open()
     Worksheets("Sheet1").Unprotect
 End Sub

こんなコードで試してみましたが、うまくいきません。
BeforeSaveイベントで
シート保護→保存→保護解除
の動きをしているのは確認できたのですが、保存するとこのコードも消えてしまいます。
やり方が間違っていると思うので、いろいろ試してみます。
ありがとうございました。

（フォーキー） 2021/12/10(金) 23:35

 >保存するとこのコードも消えてしまいます。
 マクロ有効ブック(xlsm)で保存してますか？
　
 ThisWorkbook.Saveする前に
 Application.EnableEvents = False
 しないと、再帰的にBeforeSaveが呼ばれて Cacel=Trueになるので
 いつまでも保存されません

 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
     Cancel = True
     ThisWorkbook.Worksheets("Sheet1").Protect
     Application.EnableEvents = False              ' これ大事
     ThisWorkbook.Save
     Application.EnableEvents = True　　　　　　　 ' 復帰　　
     ThisWorkbook.Worksheets("Sheet1").Unprotect
 End Sub
（´・ω・`） 2021/12/11(土) 00:27

できました。
ありがとうございます。
（フォーキー） 2021/12/11(土) 07:35

再び失礼します。

 Option Explicit

 Private Sub Workbook_Open()
     Worksheets("Sheet1").Unprotect
 End Sub

 Private Sub Workbook_BeforeClose(Cancel As Boolean)
     Worksheets("Sheet1").Protect
 End Sub

 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
     Cancel = True
     ThisWorkbook.Worksheets("Sheet1").Protect
     Application.EnableEvents = False
     ThisWorkbook.Save
     Application.EnableEvents = True
     ThisWorkbook.Worksheets("Sheet1").Unprotect
 End Sub

上のコードで、マクロが有効下でのみ編集させることができました。ありがとうございます。
少し不可解な状況に当たったので教えてください。

閉じるボタンを押して、保存するかしないかのダイアログボックスの「保存」を押したとき、他のエクセルファイルが開いた状態だと、ダイアログが無限に表示されます。
会社のExcel2016、Windows10だと、他に開いているエクセルファイルが未保存のファイルでも発生します。
自宅のWindows11、Excel2010だと未保存のエクセルファイルの時は発生しませんが、名前をつけて保存済みのファイルだと無限ループが発生します。

ステップ実行でコードを追っても原因がよくわかりません。
解決策があれば教えてください。

（フォーキー） 2022/01/26(水) 17:47

保存後に変更（保護解除）が発生してるので、
閉じようとしたら保存確認ダイアログがでるのは道理だと思いますよ。
解決策と言うには雑ですが、一度保存して、
2回目に出てくるダイアログで「保存しない」を選択するのはどうでしょうか？
（なんとなく） 2022/01/26(水) 18:20

なんとなくさん、複数のブックを開いていたら、ダイアログ発生は当然という意味でしょうか。
当方では↓のような状況が発生します。

・マクロブックのみ
　閉じるボタンクリック→ダイアログ表示（必ず発生）→保存をクリック→クローズ

・他のエクセルファイル起動時
　閉じるボタンクリック→ダイアログ表示→保存→ダイアログ表示→保存→ダイアログ表示……

という状況が発生します。
（フォーキー） 2022/01/26(水) 19:00

 Workbook_BeforeSaveイベントの最後に、

 ThisWorkbook.Saved = True

 を追加して確認してみてください。

https://docs.microsoft.com/ja-jp/office/vba/api/excel.workbook.saved

（tkit） 2022/01/27(木) 08:42

コメントありがとうございます。

 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
     Cancel = True
     With ThisWorkbook
        .Worksheets("Sheet1").Protect
        Application.EnableEvents = False
        ThisWorkbook.Save
        Application.EnableEvents = True
        .Worksheets("Sheet1").Unprotect
        .Saved = True   '追加
    End With
 End Sub

 ThisWorkbook.Saved = True　を追加してみましたが、挙動は変わりませんでした。
（フォーキー） 2022/01/27(木) 08:50

 保存前イベントでは、Sheet1をプロテクトします。
 ↓
 保存
 ↓
 保存後イベントで、プロテクト解除+保存後変更無し状態にします。

 クローズ前イベントは必要無く、希望の動作となると思います。

 Private Sub Workbook_Open()
     Me.Worksheets("Sheet1").Unprotect
 End Sub

 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
     With Me
         .Worksheets("Sheet1").Protect
     End With
 End Sub

 Private Sub Workbook_AfterSave(ByVal Success As Boolean)
     With Me
         .Worksheets("Sheet1").Unprotect
         .Saved = True
     End With
 End Sub

（tkit） 2022/01/27(木) 09:36

tkitさんありがとうございます。
希望通りの動作になりました。
BeforeSaveとAfterSaveを組み合わせるんですね。
しかも、Workbook.Saved プロパティで、変更なしならダイアログを非表示にできるとは。
前のコードでは、変更していなくても必ずダイアログが表示されていたので、とても便利です。
ありがとうございました。
（フォーキー） 2022/01/27(木) 09:52

コメント返信：

[ 一覧(最新更新順) ]

[[20211210221919]] 『マクロを有効にして操作させる』（フォーキー） ページの最後に飛ぶ

[[20211210221919]]　『マクロを有効にして操作させる』（フォーキー）　ページの最後に飛ぶ