Excel VBAのコーディング事例をご紹介しています。
事例のテーマを決めて、その機能を実現するためのコーディングを動く形でご紹介しているのですが、今回のコーディング事例はOffice VBAリファレンスに載っているような簡単なもののみになりますので、お含み置きいただければ幸いです。
そうは言いながら、画面の操作に留まらず「シートの保護」を設定する画面のチェック状態を取得するためにはWorksheetオブジェクトを中心としていつくかのメソッド・プロパティを駆使する必要がありますので備忘録も兼ねてまとめて置きたく存じます。
※動作は32bit版Excel 2016の バージョン2207(ビルド 15427.20210)を使用して検証しています。
「シートの保護」を設定する画面のデフォルト状態
「シートの保護」を設定する画面にデフォルトで表示される状態は、下記Microsoft Officeサポートのページの「ワークシートの保護を有効にする」に書かれているように、既定では15箇所の内の「ロックされたセル範囲の選択」と「ロックされていないセル範囲の選択」の2箇所になります。
校閲タブメニュから保護グループの「シートの保護」をクリックすると左図ウインドウが開きます。赤枠の「?」をクリックすると上記リンクに遷移します。
本題から少し離れますがデフォルトの2つ以外の設定をVBAで操作するためには「WorksheetオブジェクトのProtectメソッド」を使用します。
言い換えますと、左図の状態からVBAで残り13箇所のチェックを有効化する事ができますが、その時に必要なメソッドはProtectメソッドだけです。
実はProtectメソッドで使用する引数(パラメータ)の名前は本題でも使用するため、まずはProtectメソッドの内容についてご説明いたします。
「シートの保護」を設定するためのProtectメソッド
Protectメソッドは「シートの保護」を設定するとともに、デフォルトでは設定されていない項目の「有効化」または「無効化」を指定する事ができます。
少し本題からは外れるのですが、まずは「シートの保護」の各項目を設定するところからご説明いたします。
メソッドの詳細は下記Microsoft Docsのページをご参照いただければ幸い。
Protectメソッドの引数(パラメータ)は下記の16個が存在します。なお引数の値はすべてブール型 (Boolean) です。
- 下記の表は上記ページを抜粋引用したものです。
- 話の都合上、青字の項目につきましては説明を割愛いたしますが、No3のContentsは
この画面キャプチャの「シートとロックされたセルの内容を保護する」のチェック状態を操作します。そしてこのチェック状態を保持しているのがWorksheetオブジェクトのProtectContentsプロパティになります。
| No | 操作対象 | 引数の名前 | 説明 |
|---|---|---|---|
| 1 | パスワード | Password | オプションで入力 |
| 2 | オブジェクトの編集 | DrawingObjects | Trueは操作不可。既定はTrue |
| 3 | コンテンツ(グラフ含む) | Contents | Trueは保護(操作不可) 既定はTrue |
| 4 | シナリオの編集 | Scenarios | Trueは操作不可。既定はTrue |
| 5 | UIは不可・マクロのみ可 | UserInterfaceOnly | TrueはUI不可・マクロ可 |
| 6 | セルの書式設定 | AllowFormattingCells | Trueは操作可。既定はFalse |
| 7 | 列の書式設定 | AllowFormattingColumns | 〃 |
| 8 | 行の書式設定 | AllowFormattingRows | 〃 |
| 9 | 列を挿入 | AllowInsertingColumns | 〃 |
| 10 | 行を挿入 | AllowInsertingRows | 〃 |
| 11 | ハイパーリンクの挿入 | AllowInsertingHyperlinks | 〃 |
| 12 | 列の削除 | AllowDeletingColumns | 〃 |
| 13 | 行の削除 | AllowDeletingRows | 〃 |
| 14 | 並べ替え | AllowSorting | 〃 |
| 15 | オートフィルターの使用 | AllowFiltering | 〃 |
| 16 | ピボットテーブル・ピボットグラフを使用 | AllowUsingPivotTables | 〃 |
上記を使用したコーディング事例としてはつぎのようになります。
Sub SetProtect()
With Worksheets("Sheet1")
If .ProtectContents = False Then
.Protect AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, _
AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, _
DrawingObjects:=False, Scenarios:=False
Else
Debug.Print "既にシートの保護がされています。"
End If
End With
End Sub
上記を実行すると「シートの保護」を設定するため画面のすべての項目にチェックマークが付きます。
- なお3行目で使用しているProtectContentsプロパティはWorksheetオブジェクトのプロパティでTrueの場合は「シートの保護」が設定されている事を示す取得のみ可能な値になります。
- 詳細は下記Microsoft Docsのページをご参照ください。
つぎの章では上記引数にでてこなかった2つの項目についてご説明いたします。
「ロックされた/されていないセル範囲の選択」のチェック状態を確認するには
デフォルトでチェックされている「ロックされたセル範囲の選択」と「ロックされていないセル範囲の選択」を確認するためにはWorksheetオブジェクトのEnableSelectionプロパティを参照します。
Microsoft Docsのページは下記になります。
実はEnableSelectionプロパティは読み込みだけではなく書き込みもできるので、状態を確認するだけではなく「シートの保護」の設定をする事もできます。
EnableSelectionプロパティに「xlNoSelection・xlUnlockedCells・xlNoRestrictions」という引数(パラメータ)を指定した時の「シートの保護」画面の状態はつぎのようになります。
- なお設定は「シートの保護」がされていても、いなくてもどちらでも実行する事ができます。
xlNoSelection
(=-4142)
xlUnlockedCells
(=1)
xlNoRestrictions
(=0)
同様にEnableSelectionプロパティの値を確認すればいづれの状態なのかを確認する事ができます。
状態の確認を含めたコーディング事例としてはつぎのようになります。
Sub checkEnableSelection()
With Worksheets("Sheet1")
If .ProtectContents = False Then
If .EnableSelection = xlNoSelection Then .EnableSelection = xlUnlockedCells
.Protect
Else
If .EnableSelection = xlUnlockedCells Then .EnableSelection = xlNoRestrictions
End If
End With
End Sub
「シートの保護」がされていないxlNoSelectionの状態から上記を2回実行すると、「シートの保護」がされたxlNoRestrictionsの状態になります。
次の章からはProtectメソッドの引数のところでご説明した内容に入ります。
Protection オブジェクトのプロパティ
先の章でProtectメソッドの一覧をご説明しましたが、実行されたメソッドの操作対象の状態の多くはProtectionオブジェクトのプロパティとして保持されています。
「多くは」というのは一覧の中のNo6～No16までの11の項目が保持されていて、残りの2つはWorksheetオブジェクトの別のプロパティに保持されています。
ここではまず先にProtectionオブジェクトのプロパティについてご説明いたします。
Microsoft Docsのページは下記になります。
実はProtectionオブジェクトのプロパティは12個存在していて、一覧のNo6～No16の11の項目よりも1個多くなっています。
この1個多いのはProtection.AllowEditRanges プロパティになりますが、これは「シートの保護」とは別の「範囲の編集を許可する」仕組みで使用されています。
この辺りは下記のOffice TANAKAのページで詳しく説明されていますので、ご興味があればご参照いただければ幸いです。
なおProtectionオブジェクトのプロパティと一覧のNo6～No16の名前とは完全に一致していますのでここではコーディング事例だけをご紹介いたします。
Sub protectionCheck()
With Worksheets("Sheet1")
If .ProtectContents Then
Debug.Print "1." & .Protection.AllowFormattingCells ''セルの書式を設定
Debug.Print "2." & .Protection.AllowFormattingColumns '列の書式を設定
Debug.Print "3." & .Protection.AllowFormattingRows '行の書式を設定
Debug.Print "4." & .Protection.AllowInsertingColumns '列を挿入
Debug.Print "5." & .Protection.AllowInsertingRows '行を挿入
Debug.Print "6." & .Protection.AllowInsertingHyperlinks 'ハイパーリンクを挿入
Debug.Print "7." & .Protection.AllowDeletingColumns '列を削除
Debug.Print "8." & .Protection.AllowDeletingRows '行を削除
Debug.Print "9." & .Protection.AllowSorting '並べ替え
Debug.Print "10." & .Protection.AllowFiltering 'フィルターを設定
Debug.Print "11." & .Protection.AllowUsingPivotTables 'ピボットテーブル レポートを使用
Else
Debug.Print "シートの保護がされていません"
End If
End With
End Sub
- Protectionオブジェクトのプロパティは「シートの保護」がされていないと有効ではありません。そのため3行目のProtectContentsで状態を確認しています。
- 結果はVBAのイミディエイトウィンドウに出力します。
ProtectDrawingObjectsとProtectScenarios プロパティ
残すところは「オブジェクトの編集」と「シナリオの編集」の2つになりますが、これらはProtectionオブジェクトではなくWorksheetオブジェクトのProtectDrawingObjectsとProtectScenariosプロパティで状態を保持しています。
それぞれのMicrosoft Docsのページは下記になります。
コーディング事例としてはつぎのようになります。
Sub protectionCheck1()
With Worksheets("Sheet1")
If .ProtectContents Then
Debug.Print .ProtectScenarios 'True の場合は、シナリオが保護されている
Debug.Print .ProtectDrawingObjects 'True の場合は、図形が保護されている
Else
Debug.Print "シートの保護がされていません"
End If
End With
End Sub
- ProtectDrawingObjectsとProtectScenariosのプロパティは「シートの保護」がされていないと有効ではありません。
- 結果はVBAのイミディエイトウィンドウに出力します。
最後に
今回は「シートの保護」にまつわる設定状態の把握の仕方についてご紹介いたしました。
- WorksheetオブジェクトのProtectContentsプロパティ
- WorksheetオブジェクトのEnableSelectionプロパティ
- Protectionオブジェクトのプロパティの12個の内の11個
- WorksheetオブジェクトのProtectDrawingObjectsプロパティ
- WorksheetオブジェクトのProtectScenariosプロパティ
「シートの保護」の設定画面の操作はVBAではProtectメソッドにまとめられているのに、なぜその状態の把握をするためのプロパティがいろいろと存在するのか?は、一言では片づけられないExcelの歴史の年輪の現れなのかもしれません…
なおVBAで「シートの保護」されたワークシートを操作する際にはいろいろと注意すべき事がある事と存じます。
特にProtectメソッドのUserInterfaceOnlyの説明は今回割愛いたしましたが、非常に意味深いところがある認識です。(UserInterfaceOnlyの状態を確認するのはWorksheet.ProtectionMode プロパティになります。)
そのため次回のテーマとしては『「シートの保護」をした状態でVBAでRangeオブジェクトを操作した時の動き方』にしたいと思います。
以上最後までご一読いただき誠にありがとうございました。
