和風スパゲティのレシピ

日本語でコーディングするExcelVBA

トップ > ワークシート > シートの保護設定を調べる/同じ設定でシートを保護する

シートの保護設定を調べる/同じ設定でシートを保護する

ワークシート サンプルコード VBA

ワークシートの保護設定の内容を調べる方法と、
それを利用して同じ設定でシートを保護する方法を解説します。

シートの保護設定を調べる(Protectionプロパティ)

シート保護に関する各設定がONになっているかどうかを調べるには、
主にWorksheetオブジェクトのProtectionプロパティを使用します。

例えば「列の削除」が制限されているかを調べて、
されていなければ列削除を実行するコードがこちらです。

If Worksheets("○○").Protection.AllowDeletingColumns = True Then
    Worksheets("○○").Columns("A").Delete
Else
    MsgBox "列の削除はシートの保護設定で制限されています。"
End If

　

シートの保護を行う際に表示される、

シートの保護ダイアログ

このダイアログの表示名と、設定を調べるプロパティの対応表が以下のとおりです。

ダイアログの表示 調べるプロパティ
セルの書式設定 ws.Protection.AllowFormattingCells
列の書式設定 ws.Protection.AllowFormattingColumns
行の書式設定 ws.Protection.AllowFormattingRows
列の挿入 ws.Protection.AllowInsertingColumns
行の挿入 ws.Protection.AllowInsertingRows
ハイパーリンクの挿入 ws.Protection.AllowInsertingHyperlinks
列の削除 ws.Protection.AllowDeletingColumns
行の削除 ws.Protection.AllowDeletingRows
並び替え ws.Protection.AllowSorting
オートフィルターの使用 ws.Protection.AllowFiltering
ピボットテーブル(グラフ)の使用 ws.Protection.AllowUsingPivotTables
オブジェクトの編集 ws.ProtectDrawingObjects
シナリオの編集 ws.ProtectScenarios
ロックされたセル範囲の選択 ws.EnableSelection
ロックされていないセル範囲の選択 ws.EnableSelection

　
Protectionプロパティで調べることのできるシート保護の設定は、
シートの保護を実行するProtectメソッドの引数とスペルが完全に一致しています。

名称もストレートに訳せてわかりやすい英語なので、
特に迷うことはないかと思います。


ただし、元々Protectメソッドで設定しない「セルの選択制限」と、
加えて「オブジェクトの編集」「シナリオの編集」は、
Protectionプロパティではなくそれぞれ専用のプロパティで調べる必要があります。


Protection.で表示されるこの候補には出てきませんのでご注意ください。
Protectionプロパティの候補


また、EnableSelectionプロパティで調べる「セルの選択制限」は、
他のプロパティと違い、値がTrue/Falseの2択ではありません。

制限の内容 EnableSelectionの値
すべてのセル選択を禁止 xlNoselection
ロックされたセルの選択を禁止 xlUnlockedCells
すべてのセル選択を許可 xlNoRestrictions

以上3つの値を持ちますので、こちらもご注意ください。



なおこれらの設定は、シートが保護されていなくても調べることができます。


シートの保護ダイアログ
このダイアログを実際に操作するとき、
「保護を解除しても各チェックを記憶してくれている」ことからもわかりますが、
シートの保護を解除しても各プロパティの設定は変わらず保存されています。


逆に言うと、本当にその操作ができないかどうかを調べるためには、
保護設定の内容だけでなく、そもそもシートの保護が有効かを調べる必要があります。

シートが保護されているかどうかを調べたい場合は、
ProtectContentsプロパティを使用してください。

If Worksheets("○○").ProtectContents = True Then
    MsgBox "シートが保護されているため、処理を中断します。"
    Exit Sub
End If

同じ設定でシートを保護する

あるシートの設定と同じ設定でシートを保護するには、
上記で調べた各プロパティの値を、Protectメソッドの引数にそのまま代入すればOKです。

' 「設定取得シート」と同じ設定で「保護シート」を保護するコード
保護シート.Protect _
    AllowFormattingCells:=設定取得シート.Protection.AllowFormattingCells, _
    AllowFormattingColumns:=設定取得シート.Protection.AllowFormattingColumns, _
    AllowFormattingRows:=設定取得シート.Protection.AllowFormattingRows, _
    AllowInsertingColumns:=設定取得シート.Protection.AllowInsertingColumns, _
    AllowInsertingRows:=設定取得シート.Protection.AllowInsertingRows, _
    AllowInsertingHyperlinks:=設定取得シート.Protection.AllowInsertingHyperlinks, _
    AllowDeletingColumns:=設定取得シート.Protection.AllowDeletingColumns, _
    AllowDeletingRows:=設定取得シート.Protection.AllowDeletingRows, _
    AllowSorting:=設定取得シート.Protection.AllowSorting, _
    AllowFiltering:=設定取得シート.Protection.AllowFiltering, _
    AllowUsingPivotTables:=設定取得シート.Protection.AllowUsingPivotTables, _
    DrawingObjects:=設定取得シート.ProtectDrawingObjects, _
    Scenarios:=設定取得シート.ProtectScenarios

保護シート.EnableSelection = 設定取得シート.EnableSelection

　

さてこれで「Aシートの保護設定をBシートにも適用する」ことはできたのですが、
実務でより多く登場するのは「Aシートの保護設定でAシートを保護する」、
すなわち「保護を解除した後また保護し直す」処理です。


これも実は簡単で、前述の通りこのコードは「設定取得シート」のシートの保護が解除されていても問題なく動きます。

よって「保護を解除した後また保護し直す」ためには、
単純に保護シートと設定取得シートを同じシートにするだけでOKとなります。

' シートの保護を解除
処理シート.Unprotect

処理シートを書き換えるマクロ

' 再度同じ設定で保護
処理シート.Protect _
    AllowFormattingCells:=処理シート.Protection.AllowFormattingCells, _
    AllowFormattingColumns:=処理シート.Protection.AllowFormattingColumns, _
    AllowFormattingRows:=処理シート.Protection.AllowFormattingRows, _
    AllowInsertingColumns:=処理シート.Protection.AllowInsertingColumns, _
    AllowInsertingRows:=処理シート.Protection.AllowInsertingRows, _
    AllowInsertingHyperlinks:=処理シート.Protection.AllowInsertingHyperlinks, _
    AllowDeletingColumns:=処理シート.Protection.AllowDeletingColumns, _
    AllowDeletingRows:=処理シート.Protection.AllowDeletingRows, _
    AllowSorting:=処理シート.Protection.AllowSorting, _
    AllowFiltering:=処理シート.Protection.AllowFiltering, _
    AllowUsingPivotTables:=処理シート.Protection.AllowUsingPivotTables, _
    DrawingObjects:=処理シート.ProtectDrawingObjects, _
    Scenarios:=処理シート.ProtectScenarios

先ほど紹介したコードの「保護シート」と「設定取得シート」のどちらにも同じシートを書いただけですね。

「保護を解除する前に設定値を変数に記憶しておく」
といった処理は必要はありませんのでご安心ください。


ちなみに、元の「シートの保護ダイアログ」に記憶機能があったので、
「こんな面倒なことをしなくてもただ保護してはダメなの？」
と思いたくなります。

しかし、手作業でやる時のイメージで、

処理シート.Unprotect

処理シートを書き換えるマクロ

処理シート.Protect

と単にProtectとだけ書いた場合は「すべての操作を禁止した保護」が実行されてしまいますのでご注意下さい。

汎用関数化

上記のコードで「同じ設定で保護し直す」ことができると書きましたが、
いちいちあの量のコードを書くくらいなら、元の保護設定をちゃんと調べて、
各プロパティにもう一度True/Falseを渡した方が楽な気がします。


ということで、
「シートの保護設定を調べなくても同じ設定で保護し直す処理ができる」
という利便性を追求するためには、上記のコードを関数化しておく必要があります。

Sub 同じ設定でシートを保護する(保護シート As Worksheet , 設定取得シート As Worksheet)

    保護シート.Protect _
        AllowFormattingCells:=設定取得シート.Protection.AllowFormattingCells, _
        AllowFormattingColumns:=設定取得シート.Protection.AllowFormattingColumns, _
        AllowFormattingRows:=設定取得シート.Protection.AllowFormattingRows, _
        AllowInsertingColumns:=設定取得シート.Protection.AllowInsertingColumns, _
        AllowInsertingRows:=設定取得シート.Protection.AllowInsertingRows, _
        AllowInsertingHyperlinks:=設定取得シート.Protection.AllowInsertingHyperlinks, _
        AllowDeletingColumns:=設定取得シート.Protection.AllowDeletingColumns, _
        AllowDeletingRows:=設定取得シート.Protection.AllowDeletingRows, _
        AllowSorting:=設定取得シート.Protection.AllowSorting, _
        AllowFiltering:=設定取得シート.Protection.AllowFiltering, _
        AllowUsingPivotTables:=設定取得シート.Protection.AllowUsingPivotTables, _
        DrawingObjects:=設定取得シート.ProtectDrawingObjects, _
        Scenarios:=設定取得シート.ProtectScenarios, _
        Contents:=True
    
    保護シート.EnableSelection = 設定取得シート.EnableSelection

End Sub

　
別に難しいことはなく、前述のコードをそのままSubプロシージャにしただけです。
（Contents:=Trueについては後述）

こんな簡単な関数ですが、これを持っておけば、
例えばAシートと同じ設定でBシートを保存する場合は、

Call 同じ設定でシートを保護する(Bシート, Aシート)

と1行で済むようになりますし、
上記の「同じ設定で保護し直す」場合も、

処理シート.Unprotect

処理シートを書き換えるマクロ

Call 同じ設定でシートを保護する(処理シート, 処理シート)

と簡単に書けるようになります。


この手の「簡単だけど書くのが面倒で暗記は不可能」なコードは、
関数化が簡単な割にメリットが大きいです。

汎用関数集を作っている方は、ぜひそのメンバーに加えてあげてください。


汎用関数集の作り方・使い方についてはこちらをどうぞ。
www.limecode.jp


余談ですが、この関数は、

Call 同じ設定でシートを保護する(処理シート, 処理シート)

このように「同じシートを2回渡す」ことの方が多い関数です。


こういう時は、
「第2引数を省略した場合は第1引数と同じシートを渡したとみなす」
という仕様にすると、ちょっとだけ便利な関数にできます。

たいして難しくないコードですので、眺めてみてください。

Sub 同じ設定でシートを保護する(保護シート As Worksheet _
    , Optional 設定取得シート As Worksheet) ' ←Optionalをつけると省略可能になる

    ' 省略した場合(=Nothing)は第1引数を第2引数へ代入
    If 設定取得シート Is Nothing Then: Set 設定取得シート = 保護シート

    保護シート.Protect _
        AllowFormattingCells:=設定取得シート.Protection.AllowFormattingCells, _
        AllowFormattingColumns:=設定取得シート.Protection.AllowFormattingColumns, _
        AllowFormattingRows:=設定取得シート.Protection.AllowFormattingRows, _
        AllowInsertingColumns:=設定取得シート.Protection.AllowInsertingColumns, _
        AllowInsertingRows:=設定取得シート.Protection.AllowInsertingRows, _
        AllowInsertingHyperlinks:=設定取得シート.Protection.AllowInsertingHyperlinks, _
        AllowDeletingColumns:=設定取得シート.Protection.AllowDeletingColumns, _
        AllowDeletingRows:=設定取得シート.Protection.AllowDeletingRows, _
        AllowSorting:=設定取得シート.Protection.AllowSorting, _
        AllowFiltering:=設定取得シート.Protection.AllowFiltering, _
        AllowUsingPivotTables:=設定取得シート.Protection.AllowUsingPivotTables, _
        DrawingObjects:=設定取得シート.ProtectDrawingObjects, _
        Scenarios:=設定取得シート.ProtectScenarios, _
        Contents:=True

    保護シート.EnableSelection = 設定取得シート.EnableSelection

End Sub

　

さて、ややこしくなるため説明を省略していましたが、
上記関数にしれっと書いた「Contents:=True」を最後に説明します。

これはVBAのバグ対策のおまじないです。


実はProtectメソッドには不具合が存在し、
保護されたシートに対してさらにProtectメソッドを実行する際、
引数DrawingObjectsまたはScenariosを指定したときだけ、
なぜかシートの保護が外れてしまう不具合が起きます。


一応解説記事のリンクを置いておきますが、解決策として、
このバグは「Contents:=True」を書いておくと発生しません。


よってこの関数を使う上では心配する必要はありませんが、
Protectの連続実行はバグがあるというのは心の片隅においておいてください。


こういった罠に対して「罠の存在を忘れても大丈夫」というのも汎用関数の良さですね。
www.limecode.jp