Contents
この記事でできること
- VBAで全シートを一括保護・一括解除できる
- 特定シート（入力用・設定用など）を保護対象から除外できる
- UserInterfaceOnly:=True で、VBAからは編集可能な保護をかけられる
対象: Excel 2016以降 / Microsoft 365、Windows 10/11
完成イメージ（Before / After）
Before（手動でシート保護）:
- 「集計」シートを右クリック →「シートの保護」→ パスワード入力
- 「マスタ」シートを右クリック → 同じ操作
- 「入力」シートは保護しない（手動で除外）
- ブックを閉じて開き直すと…保護が外れている（UserInterfaceOnlyの場合）
- 毎回手動でかけ直し。保護し忘れてヒヤリ
After（VBAで自動保護）:
- マクロを実行（またはブック起動時に自動実行）
- 指定シートだけパスワード付きで保護される
- VBAからは自由に書き込める（UserInterfaceOnly）
- 保護し忘れゼロ
共有ブックで集計シートを勝手に編集されて数式が壊れた経験がある。保護をかけ忘れるたびにヒヤリとしていた。VBAで毎朝ブック起動時に自動保護するようにしてからは、保護し忘れがゼロになった。同じ悩みの人に、シート保護の自動化を体験してほしい。
「保護し忘れた…」のヒヤリは、VBAに任せればゼロにできる。
全シートをループして一括処理する基本は 複数シートに同じ処理を一括実行する方法 を参照。本記事ではその応用として「保護」に特化する。
実行前の準備
バックアップを取る
シート保護の設定を変更するため、意図しない保護状態になる可能性がある。必ずファイルのコピーを別フォルダに保存してから実行する。
Excelをマクロ有効ブック（.xlsm）で保存する
拡張子が .xlsx のままだとマクロが保存できない。
- 「ファイル」→「名前を付けて保存」
- ファイルの種類を「Excelマクロ有効ブック (*.xlsm)」に変更
- 保存
手順（コピペ → 実行まで約5分）
VBE（コードを書く画面）を開く
- Excelで `Alt + F11` を押す
- VBE（Visual Basic Editor）が開く
標準モジュールを挿入する
- VBEのメニュー →「挿入」→「標準モジュール」
- 白い画面（コードウィンドウ）が表示される
コードを貼り付けて実行する
- コードウィンドウに、下のコードをそのままコピペする
- `Alt + F8` → マクロ名を選んで「実行」
ボタンに割り当てれば毎回Alt+F8を押さなくて済む。方法は マクロをボタン1つで実行する方法 を参照。
コード（最小版）– 全シート一括保護・解除
'============================================================
' ■ 全シート一括保護（最小版）
' → 全シートをパスワード付きで保護する
'============================================================
Sub ProtectAllSheets()
Dim ws As Worksheet
Dim count As Long
count = 0
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="pass"
count = count + 1
Next ws
MsgBox count & " シートを保護しました。", vbInformation
End Sub
'============================================================
' ■ 全シート一括解除（最小版）
' → 全シートの保護を解除する
'============================================================
Sub UnprotectAllSheets()
Dim ws As Worksheet
Dim count As Long
count = 0
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:="pass"
count = count + 1
Next ws
MsgBox count & " シートの保護を解除しました。", vbInformation
End Sub書き換えポイント
| 変数・箇所 | 説明 | 初期値 |
|---|---|---|
| `Password:=”pass”` | 保護パスワード。ProtectとUnprotectで同じ文字列にする | `”pass”` |
コードの流れ
- `For Each ws In ThisWorkbook.Worksheets` で全シートを1枚ずつ取得
- `ws.Protect Password:=”pass”` でパスワード付き保護をかける
- 解除は `ws.Unprotect Password:=”pass”` で同じパスワードを指定
- 処理したシート数をカウントし、完了メッセージを表示
重要: ProtectとUnprotectのパスワードは必ず一致させる。不一致だとUnprotect時にエラーになる。
コード（実務版）– 特定シート除外＋UserInterfaceOnly＋パスワード管理
実務では「入力」「設定」のようなシートは保護したくない。また、VBAで集計処理をしているシートは UserInterfaceOnly:=True でVBAからの編集を許可したい。
自分はこのオプションを知らず、保護したシートにVBAで書き込もうとしてエラー1004が出て詰んだことがある。UserInterfaceOnlyを付けるだけで解決した。エラー処理（エラー処理で止まらないマクロを作る方法）と組み合わせれば、既に保護済みのシートがあっても安全に処理できる。
'============================================================
' ■ 特定シート除外＋UserInterfaceOnly で一括保護（実務版）
' → 除外シートは保護しない
' → UserInterfaceOnly:=True でVBAからは編集可能
' → 既に保護済みのシートも安全に処理
'============================================================
Sub ProtectSheetsAdvanced()
'--- ★書き換えポイント1: パスワード ---
Const PW As String = "myPassword123"
'--- ★ここまで ---
'--- ★書き換えポイント2: 保護から除外するシート名 ---
Dim excludeSheets As Variant
excludeSheets = Array("入力", "設定")
'--- ★ここまで ---
Dim ws As Worksheet
Dim count As Long
count = 0
For Each ws In ThisWorkbook.Worksheets
'--- 除外シートをスキップ
If IsInArray(ws.Name, excludeSheets) Then GoTo NextSheet
'--- 既に保護されている場合は一度解除（エラー回避）
On Error Resume Next
ws.Unprotect Password:=PW
On Error GoTo 0
'--- シートを保護（UserInterfaceOnly:=True）
ws.Protect Password:=PW, _
UserInterfaceOnly:=True, _
AllowFiltering:=True, _
AllowSorting:=True
count = count + 1
NextSheet:
Next ws
MsgBox count & " シートを保護しました。" & vbCrLf & _
"（除外シートは保護していません）" & vbCrLf & _
"※ VBAからは編集可能です", vbInformation
End Sub
'============================================================
' ■ 特定シート除外で一括解除（実務版）
' → 除外シート以外の保護を解除
'============================================================
Sub UnprotectSheetsAdvanced()
Const PW As String = "myPassword123"
Dim excludeSheets As Variant
excludeSheets = Array("入力", "設定")
Dim ws As Worksheet
Dim count As Long
count = 0
For Each ws In ThisWorkbook.Worksheets
If IsInArray(ws.Name, excludeSheets) Then GoTo NextSheet2
On Error Resume Next
ws.Unprotect Password:=PW
On Error GoTo 0
count = count + 1
NextSheet2:
Next ws
MsgBox count & " シートの保護を解除しました。", vbInformation
End Sub
'============================================================
' ■ 補助関数: 値が配列に含まれるかチェック
'============================================================
Private Function IsInArray(val As String, arr As Variant) As Boolean
Dim i As Long
For i = LBound(arr) To UBound(arr)
If arr(i) = val Then
IsInArray = True
Exit Function
End If
Next i
IsInArray = False
End Function書き換えポイント
| 変数・箇所 | 説明 | 初期値 |
|---|---|---|
| `PW` | 保護パスワード。Const で定数として1箇所で管理 | `”myPassword123″` |
| `excludeSheets` | 保護から除外するシート名の配列。追加・削除は `Array(…)` 内を変更するだけ | `Array(“入力”, “設定”)` |
| `AllowFiltering` | 保護中のオートフィルタ許可 | `True` |
| `AllowSorting` | 保護中の並べ替え許可 | `True` |
コードの流れ
- **パスワード定義**: `Const PW` で定数として1箇所で管理。変更時は1箇所だけ修正すればOK
- **除外シートの定義**: `Array(“入力”, “設定”)` で保護しないシートを指定
- **除外チェック**: `IsInArray` で除外対象ならスキップ
- **既存保護の解除**: `On Error Resume Next` で既に保護済みでもエラーを回避し、一度解除
- **保護の実行**: `UserInterfaceOnly:=True` でVBAからは編集可能。`AllowFiltering`と`AllowSorting`で利便性を維持
- **完了メッセージ**: 保護したシート数を表示
ブック起動時に自動保護する（Workbook_Open）
UserInterfaceOnly:=True はブックを閉じるとリセットされる。ブックを開くたびに自動で保護をかけ直すには、ThisWorkbook モジュールに以下を記述する。
貼り付け場所: VBEの左側ツリーで「ThisWorkbook」をダブルクリック → コードウィンドウに貼り付け
'============================================================
' ■ ブック起動時に自動保護
' → ThisWorkbook モジュールに貼り付ける
'============================================================
Private Sub Workbook_Open()
Call ProtectSheetsAdvanced
End Sub保護マクロをボタンに割り当てておけば、手動で実行し直すこともできる。ボタンの作り方は [マクロをボタン1つで実行する方法](/013) を参照。
よくある落とし穴5選
1. 保護したシートにVBAで書き込もうとしてエラー1004
自分もこれで詰んだことがある。集計マクロを動かしたら「実行時エラー ‘1004’: シートが保護されているため、変更できません」が出た。保護をかけたこと自体を忘れていた。
原因: ws.Protect でシートを保護すると、VBAからの書き込みもブロックされる。
対策: UserInterfaceOnly:=True を付けて保護する。これでユーザーの手動操作だけ保護され、VBAからは自由に編集できる。
2. UserInterfaceOnly:=True がブックを閉じるとリセットされる
原因: Excelの仕様で、UserInterfaceOnly:=True の設定はブックを閉じると失われる。次にブックを開いたとき、シートは保護されたままだがVBAからの書き込みもブロックされる。
対策: Workbook_Open イベントでブック起動時に毎回 Protect UserInterfaceOnly:=True を実行する。実務版コードの「ブック起動時に自動保護する」セクションを参照。
3. パスワードがVBEで丸見え
原因: VBAコード内にパスワードをハードコードするため、Alt + F11 でVBEを開けば誰でもパスワードが見える。
対策: VBAプロジェクトにパスワードをかける（VBEメニュー →「ツール」→「VBAProject のプロパティ」→「保護」タブ）。ただし、これも完璧ではない。機密性の高い保護が必要な場合は、別のセキュリティ手段を検討する。
4. 既に保護済みのシートに Protect を実行してエラー
原因: パスワード付きで保護済みのシートに、別のパスワードで Protect を実行するとエラーが出る。
対策: Protect の前に On Error Resume Next + Unprotect で既存の保護を一度解除する。実務版コードにはこのパターンを組み込み済み。
5. Unprotect のパスワードを間違えてエラー
原因: ws.Unprotect Password:="wrong" のようにパスワードが不一致だと、実行時エラーが発生する。
対策: パスワードは Const PW で定数管理し、Protect と Unprotect で同じ変数を使う。実務版コードのように1箇所で管理すれば、不一致は起きない。
FAQ
Q1: 特定のセルだけ編集可能にしたい
保護前に、編集を許可するセルの Locked プロパティを False にする：
ws.Range("B2:D10").Locked = False ' このセルは編集可能
ws.Protect Password:="pass", UserInterfaceOnly:=True保護後、B2:D10 だけはユーザーが手動で編集できる。入力規則と組み合わせる方法は 入力規則（ドロップダウンリスト）をVBAで一括設定 を参照。
Q2: 保護中でもオートフィルタを使いたい
Protect の引数に AllowFiltering:=True を追加する（実務版コードでは設定済み）：
ws.Protect Password:="pass", _
UserInterfaceOnly:=True, _
AllowFiltering:=TrueQ3: シートが保護されているかどうか確認したい
ProtectContents プロパティで確認できる：
If ws.ProtectContents Then
MsgBox ws.Name & " は保護されています"
Else
MsgBox ws.Name & " は保護されていません"
End IfQ4: パスワードを忘れてしまった
VBAコード内に Const PW でパスワードを書いているなら、VBEを開いて確認する。パスワードが不明な場合は、VBAで空パスワードでの解除を試す：
On Error Resume Next
ws.Unprotect Password:=""
On Error GoTo 0これで解除できない場合は、元のパスワードが必要。
Q5: ブックを開くたびに保護し直すのが面倒
Workbook_Open イベントを使えば自動化できる。実務版コードの「ブック起動時に自動保護する」セクションを参照。セルの値が変わったタイミングで処理を実行する方法は セルの値が変わったら自動実行 も参考になる。
まとめ
- `ws.Protect Password:=”pass”` でシート保護、`ws.Unprotect Password:=”pass”` で解除
- 除外シートは配列で管理し、`IsInArray` でスキップ
- `UserInterfaceOnly:=True` を付ければ、VBAからは編集可能（保護中でもマクロが動く）
- `UserInterfaceOnly:=True` はブックを閉じるとリセットされるため、`Workbook_Open` で自動実行が定番
関連記事
- [入力規則（ドロップダウンリスト）をVBAで一括設定](/012) — 保護＋入力規則でさらに入力制限を強化
- [複数シートに同じ処理を一括実行する方法](/015) — 全シートループの基本はこちら
- [マクロをボタン1つで実行する方法](/013) — 保護・解除マクロをボタンに割り当て
次にやりたくなること
- **[入力フォーム（UserForm）で手入力ミスを防ぐ](/020)**: 保護したシートに安全にデータを入力させるならUserFormが最適。保護を解除せずに入力できる
- **[セルの値が変わったら自動実行](/008)**: シート保護とWorksheet_Changeイベントを組み合わせれば、特定セルの変更をトリガーに処理を実行できる
もっとカスタマイズしたい場合
「シートごとに保護範囲（編集可能セル）を変えたい」「特定のユーザーだけ編集を許可したい」「保護パスワードを外部ファイルで管理したい」など、業務に合わせたカスタマイズが必要な場合は、ココナラで相談できる。
相談時に伝えると話が早い情報:
- Excel のバージョン / OS
- 保護したいシートの枚数と名前
- 編集可能にしたいセル範囲
- VBAで書き込む処理があるかどうか
コメント