[Excel VBA] How to Protect and Unprotect Worksheets with a Password

2025年12月27日

It is common to protect sheets containing formulas or important data to prevent users from accidentally changing them. Using VBA, you can automate the process of protecting and unprotecting worksheets.

In this article, I will explain the basic usage of the .Protect method for password protection and the .Unprotect method for unlocking sheets. I will also cover an advanced technique that allows macros to run without being blocked by protection.

How to Protect a Worksheet (.Protect)

The .Protect method protects a specified worksheet and restricts cell editing.

Code and Explanation

Sub ProtectSheetWithPassword()

    ' Declare variables
    Dim targetSheet As Worksheet
    Dim sheetPassword As String
    
    ' Set the sheet and password
    Set targetSheet = ThisWorkbook.Worksheets("Report")
    sheetPassword = "p@ssw0rd"

    ' --- Protect the sheet with a password ---
    targetSheet.Protect Password:=sheetPassword

    MsgBox "The '" & targetSheet.Name & "' sheet has been protected."

End Sub

targetSheet.Protect: Protects the target sheet.
Password:=sheetPassword: Specifies the password required to unprotect the sheet. If you omit this argument, the sheet will be protected without a password.

[Advanced] Enabling Macro Access While Protected

By default, protecting a sheet also blocks VBA macros from editing cells. However, by setting the UserInterfaceOnly argument to True, you can create a state where “manual editing by users is prohibited, but editing by VBA macros is allowed.”

Sub ProtectSheet_ForMacro()

    Dim targetSheet As Worksheet
    Set targetSheet = ThisWorkbook.Worksheets("Report")

    ' Protect using UserInterfaceOnly:=True
    targetSheet.Protect Password:="p@ssw0rd", UserInterfaceOnly:=True

    MsgBox "Sheet protected. (Macros can still edit the data.)"
    
    ' Writing via macro is possible even while protected
    targetSheet.Range("A1").Value = "Macro write test"

End Sub

Using this method simplifies your code and improves processing speed because you do not have to repeatedly unprotect and re-protect the sheet during macro execution.

How to Unprotect a Worksheet (.Unprotect)

The .Unprotect method returns a protected worksheet to an editable state.

Code and Explanation

Sub UnprotectSheetWithPassword()

    ' Declare variables
    Dim targetSheet As Worksheet
    Dim sheetPassword As String
    
    ' Set the sheet and password
    Set targetSheet = ThisWorkbook.Worksheets("Report")
    sheetPassword = "p@ssw0rd" ' Must match the protection password

    ' --- Unprotect the sheet using the password ---
    targetSheet.Unprotect Password:=sheetPassword

    MsgBox "The '" & targetSheet.Name & "' sheet is now unprotected."

End Sub

targetSheet.Unprotect: Unprotects the target sheet.
Password:=sheetPassword: Specifies the password set during protection. If the password is incorrect, an error will occur.

Summary

Protecting and unprotecting sheets can be achieved with two simple methods:

To protect: mySheet.Protect Password:="password"
To unprotect: mySheet.Unprotect Password:="password"

The most important technique for managing macros is the UserInterfaceOnly:=True argument. By using mySheet.Protect Password:="password", UserInterfaceOnly:=True, you can prevent user errors while maintaining the convenience of automation.

よかったらシェアしてね！