マクロでのみ編集可能なシート保護を設定する

シートの保護によりユーザーの操作を制限したシートにおいて、
マクロからのみ編集できるように設定する方法を解説します。

Protectメソッドの引数UserInterfaceOnlyを使う

シートを保護するProtectメソッドには、
「ユーザーの操作のみを制限する」という意味の、
UserInterfaceOnlyという引数があります。

この引数にTrueを渡すことで、ユーザー操作を制限しつつ、
マクロからは編集ができるシート保護を設定することができます。

Worksheets("○○").Protect UserInterfaceOnly:=True

　
この設定は保護されたシートにも実行できますが、
UserInterfaceOnly以外の引数は現在の状態を引き継いでくれるわけではありません。

' 書式設定だけ許可するシートの保護をかける
Worksheets("○○").Protect _
    AllowFormattingCells:=True, _
    AllowFormattingColumns:=True, _
    AllowFormattingRows:=True

' マクロからの編集を許可
Worksheets("○○").Protect UserInterfaceOnly:=True
    ' ↑（注）このコードにより書式設定が禁止されてしまう

　
手作業で設定したシートの保護に対しても同様ですので、
UserInterfaceOnlyの保護設定を使う場合は、
それ以外の引数もしっかりと設定する必要があります。

Worksheets("○○").Protect UserInterfaceOnly:=True, _
    AllowFormattingCells:=True, _
    AllowFormattingColumns:=True, _
    AllowFormattingRows:=True

注意！この設定はブックを閉じると消えてしまいます

上記の説明だけだと万能そうなUserInterfaceOnly設定ですが、
重大な欠点として、この設定は保存ができません。

この設定でシートを保護してファイルを保存したとしても、
再度ファイルを開いた際にはこの設定が消えてしまいます。

よってこの設定を活用したい場合は、ブックを開くと常に再設定されるよう、
イベントプロシージャ「Workbook_Open」でProtectメソッドを実行するなどの工夫が必要になります。

' ブックを開くと自動実行されるプロシージャでProtectメソッドを実行
Private Sub Workbook_Open()

    Thisworkbook.Worksheets("○○").UnProtect
    Thisworkbook.Worksheets("○○").Protect UserInterfaceOnly:=True, _
        AllowFormattingCells:=True, _
        AllowFormattingColumns:=True, _
        AllowFormattingRows:=True
        
End Sub

このプロシージャを利用すれば、ユーザーが普通にブックを開いたときは、
マクロのみ編集可能なシートを作ることができます。

しかしこれにもまだ落とし穴があります。

Workbook_Openはイベントプロシージャですので、
当然ながらApplication.EnableEventsの影響を受けます。

別ブックを開いて処理するようなマクロは、
高速化やChange系イベントの無効化を目的に、
EnableEventsをFalseにしていることが多いでしょう。

このときは当然Workbook_Openは起動されませんのでご注意ください。

よってこの方法は、自ブックのシート限定の方法と思っていた方が安全です。

他ブックの保護シートを処理する場合は、以下の方法を使用してください。

Unprotect/Protectで処理を挟む

こちらは地道な方法です。

マクロ実行直後にUnprotectメソッドでシート保護を一旦解除し、
終了直前にProtectメソッドで再度シートを保護します。

Sub 保護されたシートを編集するマクロ()

    ' シートの保護をいったん解除
    Worksheets("○○").Unprotect
    
    ' メインの処理
    ～ シートを編集するコード ～
    
    ' 再度シートの保護を実行
    Worksheets("○○").Protect _
        AllowFormattingCells:=True, _
        AllowFormattingColumns:=True, _
        AllowFormattingRows:=True

End Sub

　
ストレートなコードですのでわかりやすいと思いますが、
ひとつ注意点として、最後のProtectメソッドの引数は省略できません。

手作業でシートを保護するときに出る

このダイアログは「チェックの状態を記憶してくれる」ため、
同じイメージで

Worksheets("○○").Unprotect

～ シートを編集するコード ～

Worksheets("○○").Protect

このように書きたくなってしまうのですが、
このProtectメソッドは「解除時と同じ設定の保護」ではなく、
「すべてのチェックが外れた」シートの保護をかけます。

「書式設定は許可した保護」など設定を行っている場合は、
もう一度のその引数設定をしなければいけませんのでご注意ください。

なお、この「記憶されたチェック状態」は、Protectメソッドではなく、
Protectionプロパティにちゃんと保存されています。

これを読み取るコードを書くことで、
解除したときと同じ設定のシート保護をかけることが可能になります。

Sub 保護されたシートを編集するマクロ()

    Worksheets("○○").Unprotect

    ～ シートを編集するコード ～

    Call 同じ設定でシートを保護する(Worksheets("○○"), Worksheets("○○"))

End Sub


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

Protectメソッドの全引数へ、Protectionプロパティの全メンバーを代入しているため、
コードは長いですが中身は単純です。

とはいえこの長いコードをいちいち書いていては意味がありませんので、
この処理をよく行う方は、上記の様に関数化して持っておきましょう。

そうすることで、シートの保護解除/再保護ともに、
目的だった1行記述にすることができます。

詳しくはこちらの記事をどうぞ。
www.limecode.jp

注意すべきProtectメソッドの不具合

長くなるため最後に回しましたが、実はProtectメソッドには不具合が存在します。

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

つまり、Protectメソッドはなるべく連続実行してはいけません。

今回のコードですと、Workbook_Openにてシート保護を実行する際、
この不具合を知らないとProtectメソッドだけを書いてしまいがちです。

しかし、シートにすでに保護かかかっていた場合は、
ブックを開くと同時にシートの保護が外れてしまいますのでご注意ください。

解決策は「必ずUnprotectを挟む」「Contents:=Trueを指定する」の2つです。
（上記の関数化バージョンでは後者を採用してますので見てみてください）

このバグに関する詳しい解説はこちらをどうぞ
www.limecode.jp