投稿日: 投稿者: ota

【VBA】Excelでシートをまとめて保護・解除する方法【CommandBars】

Excelでシートの保護を複数ページで行ったときに，作業効率を考えると，「選択している（グループ化している）シートをいっぺんに保護・解除したい」というときがあると思います。今回はその機能を実装する方法を紹介します。

流れ

    • 選択してるシートを全て保護・保護解除するマクロを作成
    • 右クリックメニューで「１のマクロ」を起動するマクロを作成
    • 「２のマクロ」をExcelの起動・終了時に実行するイベントの設定

選択しているシートを全て保護・保護解除するマクロを作成

標準モジュールに下記のマクロをコピペしてください。標準モジュールはエディターの挿入から追加できます。

VBAにおいて，選択されているシートを調べるには，SelectedSheetsコレクションを参照するのですが，その親オブジェクトはThisworkbookではなく，ActiveWindowです。おそらく，同じブックのウィンドウが複数ある場合は，それぞれで，選択されているシートが異なるため，Thisworkbookオブジェクトでは，一意的に選択されているシートが定められないからだと思われます。

Sub シートの保護()
On Error GoTo myerror
Dim WS As Worksheet
Dim sName() As String
Dim i As Integer
Dim startsheet As String
    startsheet = ThisWorkbook.ActiveSheet.Name
    For Each WS In ActiveWindow.SelectedSheets
        ReDim Preserve sName(i)
            sName(i) = WS.Name
            i = i + 1
    Next WS
    For i = LBound(sName, 1) To UBound(sName, 1)
        ThisWorkbook.Worksheets(sName(i)).Select Replace:=True
        ThisWorkbook.Worksheets(sName(i)).Unprotect
        ThisWorkbook.Worksheets(sName(i)).Protect DrawingObjects:=False, _
                                                contents:=True, _
                                                Scenarios:=True
    Next i
    ThisWorkbook.Worksheets(startsheet).Select
    Exit Sub
myerror:
    MsgBox ("保護できませんでした。")
End Sub

Sub シートの保護解除()
On Error GoTo myerror
Dim WS As Worksheet
Dim sName() As String
Dim i As Integer
Dim j As Integer
Dim startsheet As String
    startsheet = ThisWorkbook.ActiveSheet.Name
    For Each WS In ActiveWindow.SelectedSheets
        ReDim Preserve sName(i)
            sName(i) = WS.Name
            i = i + 1
    Next WS
    For i = LBound(sName, 1) To UBound(sName, 1)
        ThisWorkbook.Worksheets(sName(i)).Select
        ThisWorkbook.Worksheets(sName(i)).Unprotect
    Next i
    ThisWorkbook.Worksheets(startsheet).Select
    Exit Sub
myerror:
    MsgBox ("保護解除できませんでした。")
End Sub

コードの説明をすると，シート１枚に対してシートの保護解除をするメソッドは「Protect」「Unprotect」です。選択されているシートの名前を取得し，その名前のシートを保護解除するというマクロです。

右クリックメニューで「１のマクロ」を起動するマクロを作成

下記のマクロは右クリックメニュー（改ページビューを除く）に先ほど作成したマクロを追加するマクロです。

Sub CommandAdd()
On Error GoTo myerror
    '■右クリックメニューの初期化
    CommandBars("Cell").Reset
    '■右クリックメニューに追加
    With CommandBars("Cell").Controls.Add(Before:=1)
        .Caption = "シートの保護"
        .OnAction = "シートの保護"
    End With
    With CommandBars("Cell").Controls.Add(Before:=1)
        .Caption = "シートの保護解除"
        .OnAction = "シートの保護解除"
    End With
    Exit Sub
myerror:
    MsgBox ("メニューの追加に失敗しました。")
End Sub

Sub OpenEvents()
    Call CommandAdd
End Sub

Sub EndEvents()
    CommandBars("Cell").Reset
End Sub

これらのマクロをExcelの起動時に実行し，メニューを追加し，Excelの終了時にメニューを初期化すれば，そのExcelのBookのみで有効なメニューバーが作成できます。

このまま実行してもメニューは追加できます。

「２のマクロ」をExcelの起動・終了時に実行するイベントの設定

Excelの起動・終了時にイベントを設定する場合は，「標準モジュールではなく，ThisWorkbookオブジェクトに直接書き込みます。

'起動時に実行するマクロ
Private Sub Workbook_Open()
    Call OpenEvents
End Sub
'終了時に実行するマクロ
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call EndEvents
End Sub

 

使って見ましょう

それでは，シートを複数選択（Shift+クリック）でマクロを実行してみましょう。

↓まとめて保護・解除できました。

シートの選択が離散的でも大丈夫です。（Ctrl+クリックでも可）

おまけ

お疲れ様でした。これで，右クリックメニューからシートを複数選択している状況でもシートの保護解除できます。ちなみに，右クリックメニューのアイコンを設定したいという方は，FaceIDを検索して設定してみてください。かわいいものやマクロに合ったＩＤを付けるのは楽しいですよ。