Lock and unlock every sheet with one Master Control button

Most of the management systems I build have a Master Control button. Users enter data through forms and cannot break formulas, while the admin can unlock everything with one click to make changes.

The macro

Option Explicit

' One button to lock or unlock every sheet in the workbook
' Free module from sourabsaha.com
'
' Change the password below before you use it.
' UserInterfaceOnly lets your own macros keep editing locked sheets.

Public Const MasterPassword As String = "change-this-password"

Public Sub ToggleMasterControl()
    Dim ws As Worksheet
    Dim locking As Boolean

    locking = Not ThisWorkbook.Worksheets(1).ProtectContents

    For Each ws In ThisWorkbook.Worksheets
        If locking Then
            ws.Protect Password:=MasterPassword, UserInterfaceOnly:=True, AllowFiltering:=True
        Else
            ws.Unprotect Password:=MasterPassword
        End If
    Next ws

    MsgBox IIf(locking, "All sheets are locked.", "All sheets are unlocked for editing."), vbInformation
End Sub

' UserInterfaceOnly is not saved with the file.
' Call this from Workbook_Open in the ThisWorkbook module so macros keep working after reopening.
Public Sub ReapplyProtection()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.ProtectContents Then
            ws.Protect Password:=MasterPassword, UserInterfaceOnly:=True, AllowFiltering:=True
        End If
    Next ws
End Sub

Why UserInterfaceOnly matters

Normally a protected sheet also blocks your own macros, so every macro has to unprotect and protect again. With UserInterfaceOnly:=True the sheet is locked for users but VBA can still write to it.

The catch is that Excel does not save this setting. When the file is reopened, sheets stay protected but macros are blocked again. That is what ReapplyProtection is for. Add this to the ThisWorkbook module:

Private Sub Workbook_Open()
    ReapplyProtection
End Sub

Before you lock

• Select the input cells users should edit, press Ctrl + 1 and untick Locked on the Protection tab.
• Change MasterPassword and protect the VBA project too (Tools, VBAProject Properties, Protection), otherwise anyone can read the password.
• Sheet protection keeps honest users from breaking things. It is not strong security for sensitive data.

Free download

Download Master-Control-Lock.bas

1. Open your workbook and press Alt + F11 to open the VBA editor.
2. Choose File, Import File and pick the downloaded .bas file.
3. Save the workbook as Excel Macro-Enabled Workbook (.xlsm).
4. Run the macro from Developer, Macros, or assign it to a button.