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

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

流れ


    • 選択してるシートを全て保護・保護解除するマクロを作成
    • 右クリックメニューで「1のマクロ」を起動するマクロを作成
    • 「2のマクロ」を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

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

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

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

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のみで有効なメニューバーが作成できます。

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

「2のマクロ」を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を検索して設定してみてください。かわいいものやマクロに合ったIDを付けるのは楽しいですよ。