[Excel VBA]イベントマクロ上でブック保護解除、再度ブック保護する方法

サンプル.xlsm というマクロブックがあるとします。

シートは以下の4シート構成になっています。
・メインシート
・説明書1
・説明書2
・説明書3
※初期は説明書1～3は非表示になっています

メインシート上のプルダウンリストの選択に連動して、メインシートのいくつかの行を非表示にし、「説明書1」を表示。
プルダウンを変更するとメインシートの別の行を非表示にし、「説明書1」が非表示になり、「説明書2」を表示、、、といった具合です。

第三者が各シートを編集できないように全てのシートに保護をかけており、
更にシートを操作されないように、ブックの保護もかけています。

シート保護の状態では、行の非表示ができないため、「ActiveSheet.Unprotect」で一時的に保護解除するようにしました。

同様に、シートの表示、非表示操作時に、「ActiveWorkbook.Unprotect」で一時的に保護解除し、
「ActiveWorkbook.Protect」で再度保護をかけているつもりなのですが、
保護が外れたままになってしまいます。

何が考えられるでしょうか？


ちなみに、プルダウンのイベント監視にて以下のような処理をしています。

Select Case Cells(8, 3).Value
Case "説明書1"
ActiveWorkbook.Unprotect Password:="zaisanshobun" '一時的にブック保護解除
ActiveSheet.Unprotect Password:="zaisanshobun" '一時的にシート保護解除

行表示、非表示操作
シート表示、非表示

ActiveWorkbook.Protect Password:="zaisanshobun", Structure:=False, Windows:=False 'ブック保護に戻す
ActiveSheet.Protect Password:="zaisanshobun" 'シート保護に戻す
Case "説明書2"
ActiveWorkbook.Unprotect Password:="zaisanshobun" '一時的にブック保護解除
ActiveSheet.Unprotect Password:="zaisanshobun" '一時的にシート保護解除
≪以下省略≫
End Select

改善点をご教示いただけると幸いです。

No.4 ベストアンサー 回答者： WindFaller 回答日時： 2018/04/28 16:33

こんにちは。

>ActiveWorkbook.Unprotect Password:="zaisanshobun" '一時的にブック保護解除
>ActiveSheet.Unprotect Password:="zaisanshobun" '一時的にシート保護解除

もちろん、Activeworkbook ではなく、ThisWorkbook でしょうけれども、これは、論理的(プログラム)には問題ないはずですが、両方を併記する種類のものではないはずです。以前、この手の質問があった時に、私の指摘に、質問者の方は信じないという反応で、こちらはかなり面食らいました。その後、どうなったのかは結果は知りません。

抽象的な内容になるのですが、私のコーディングで、時折、SleepやOnTime を使う理由は、物理的な時間差を取らないと、マクロが抜けてしまうことがあるからです。しかし、今回は、それ以上の重さを持ったものだと思います。

試しに、サンプルを考えてみました。情報が後出しで出てきて、内容が一変してしまう可能性もありますが、現在の質問に対する答です。

>メインシート上のプルダウンリストの選択に連動して、メインシートのいくつかの行を非表示にし、

・行表示、非表示操作（メインシート)
・シート表示、非表示（他のシート)

これを加えて、以下がそのサンプル・ソースです。
なお、パスワードは、カスタム・プロパティに収めることも出来ます。

'//標準モジュール
Option Explicit
Public Const PSWD As String = "123"

Sub OpenSheet()
　Dim shName As Variant
　Dim i As Long
　With Worksheets(1).DropDowns(1)
　　shName = .List(.Value)
　End With
　Call HiddenLinesProg(shName)
　If shName <> Worksheets(1).Name Then
　If MsgBox("ページを開きますか？", vbOKCancel) = vbCancel Then
　 GoTo EndLine
　End If
　End If
　ThisWorkbook.Unprotect PSWD
　If shName <> "" Then
　　For i = 2 To Worksheets.Count
　　　If LCase(Worksheets(i).Name) <> LCase(shName) Then
　　　 Worksheets(i).Visible = xlSheetVeryHidden
　　　Else
　　　 Worksheets(i).Visible = xlSheetVisible
　　　End If
　　Next
　　Worksheets(shName).Select
　End If
　ThisWorkbook.Protect PSWD, True, True
EndLine:
　 Call HiddenLinesProg(" ")
End Sub
Sub HiddenLinesProg(ByVal keyword As String)
With Worksheets(1)
.Range("A9").Resize(, 2).AutoFilter _
　field:=1, _
　Criteria1:=keyword, _
　VisibleDropDown:=False
End With
End Sub

'//ThisWorkbook モジュール

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets(1).Select
ThisWorkbook.Save
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheets(1).Select
End Sub

Private Sub Workbook_Open()
Dim Sh As Variant
For Each Sh In ThisWorkbook.Worksheets
Sh.Unprotect Password:=PSWD
Sh.Protect Password:=PSWD, UserInterfaceOnly:=True
Next Sh
If Worksheets(1).DropDowns(1).ListCount < 2 Then
　Call SettingCombo
End If
Worksheets(1).DropDowns(1).ListIndex = 1
End Sub
Private Sub SettingCombo()
Dim i As Long, j As Long
Dim ar()
j = Worksheets.Count - 1
ReDim ar(j)
For i = 1 To Worksheets.Count
ar(i - 1) = Worksheets(i).Name
Next
With Worksheets(1).DropDowns(1)
　.List = ar
　.OnAction = "Module1.OpenSheet"
End With
Call HiddenLinesProg("　　") '非表示のためのダミー
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim i As Long
If Sh Is Worksheets(1) Then
ThisWorkbook.Unprotect PSWD
For i = 2 To Worksheets.Count
　With Worksheets(i)
　 If .Visible < 2 Then
　　.Visible = xlSheetVeryHidden
　 End If
　End With
Next i
ThisWorkbook.Protect PSWD, True, True
Worksheets(1).DropDowns(1).ListIndex = 1
Call HiddenLinesProg("　　 ") '非表示のためのダミー
End If
End Sub

'//

見た目よりも設定が複雑で問題が出る可能性もありますので、アップロードしました。
あくまでもサンプルです。Excel 2010 で作成。

パスワードは、このスレのURLの1から始まる8桁の番号

https://bit.ly/2I2Jzkq
オープンは投稿時間より約2週間ですが、途中で削除するかもしれません。

拡張子が、zipに変わってしまっていますから、xlsm に変えてください。zip -> xlsm　プロパティの属性が変化していますので、プロパティを開けて、ブロックを解除します。
開いたら、セキュリティの警告の黄色の帯が出ていますので、「コンテンツの有効化」をクリック

これで、完了です。

この回答へのお礼

ダウンロードして開くことができましたので削除していただいても大丈夫です。
ありがとうございます。

ちなみにシート、ブックの保護は123で解除できましたので、仰るパスワードをどこで使うのか不明でした。。。

お礼日時：2018/05/08 16:40

No.5 回答者： WindFaller 回答日時： 2018/04/29 11:34

#4の補足です。

マクロを不可にして、ブックを開けてみてください。内容はみれないはずです。

No.3 回答者： fujillin 回答日時： 2018/04/27 15:23

No2です

失礼いたしました。少し勘違いしていたようです。

＞質問はブックの保護についてなんです。
とは言え、ブックでも同様なので、保護をかけなおす文が確実に実行されているかをまず確認してみましょう。
StopやDebug.Printを利用すれば容易に確認できることと思います。

更に、その直後で、実際に保護がかかっているかを確認してみることで、原因をかな絞り込めるものと思います。

この回答へのお礼

処理の最後を下記の記述にしたらうまくいきました。
ThisWorkbook.Protect Password:="PASSWORD", Structure:=True

Select case文の場合、Case毎に上記の記述をしなければいけません。
もっと良い方法ないでしょうか？

お礼日時：2018/04/27 17:20

No.2 回答者： fujillin 回答日時： 2018/04/27 09:09

こんにちは

ご質問文に省略があるので確認はできませんが・・・

ActiveSheetに対して解除/保護を行っているようですので、処理途中でActiveSheetが入れ替わっていると保護のかけ忘れが発生しやすいかと思います。（コード上でも発見しにくいですね）

ご提示の内容からだけでは、上記が原因かどうかの判断はできませんが、対象ブックはフォーマット固定だと想像しますので、きちんとシートを明示して解除/保護を行った方が宜しいかと思います。
また、処理中にあちこちで解除/保護を行うよりも、最初に必要になるところで関係するシートをまとめて解除し、シート操作の終了時にまとめて保護するようにしておく方が、将来的にもわかりやすいコードになるのではないでしょうか？

この回答へのお礼

シート保護解除、保護は上手くいってます。
質問はブックの保護についてなんです。

お礼日時：2018/04/27 09:14

No.1 回答者： puzzle_neko 回答日時： 2018/04/27 00:28

回答になっていないですが、マクロを使わない方法として参考までに。

ひとつのシートにすべて表示させておいて、
たとえば、説明書１　の行をすべて選択し、
メニューより「グループとアウトラインの設定」-「グループ化」
　（ショートカットで、Ａｌｔ－Ｄ－Ｇ－Ｇ）
とすると、グループ化されます。
左側に＋－のついたボタン？が出てくるので、表示させたり、隠したりできます。

book自体を読み込み専用ファイルにしておけば、データの書き換えをされずに
済むので、マクロを使わずにできます。

この回答へのお礼

ご親切にありがとうございます。

アドバイスいただいた方法は既に承知している上で、
その方法だと駄目なのでマクロを使用しています。

また、このブックを各所に配布して、必要事項を入力して戻してもらうための書式ですので、
読み取り専用にもできません。

ブックの保護をかけた状態を維持する方法を探しています。

お礼日時：2018/04/27 01:59