VBA Protect/Unprotect Workbook (Excel File)

To protect a workbook, you need to use VBA’s PROTECT method. By using this method, you can use a password, or even you can activate workbook protection without a password. But there’s one thing that you need to take care of the password that you use is a CASE-SENSITIVE.

Workbook.Protect "Password"

1. Specify the workbook that you want to protect.
2. Type and dot and select the protect method from the list or you can type “Protect”.
3. Enter the password that you want to set.
4. Run the code to protect the workbook.

In this tutorial, we will learn this method in different ways and we will also learn to unprotect a workbook.

Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook

Protect a Workbook with Password

Below is the code that you need to use to protect a workbook with a password. In this code, you have the workbook name first and the protection method with the password.

Workbooks("Book1").Protect "test123"

And when I run this code, it locks the “Book1” workbook.

Protect a Workbook without a Password

And if you want to protect a workbook without a password you need to skip the “Password” argument, just like the following code.

Workbooks("Book1").Protect

If you are trying to protect a workbook without a password that is already protected with a password Excel will show a dialogue box to enter that password.

Unprotect a Workbook with a Password

In the same way, you can use the unprotect method to unprotect the workbook and the code for this would be like something below.

Workbooks("Book1").Unprotect "test123"

There’s one thing that you need to take care of that if you are trying to unprotect a workbook that is w

Unprotect a Workbook without a Password

And if a workbook is protected without a password you can use the same method without specifying the password argument.

Protect All the Open Workbooks

In the code below, we have FOR EACH loop to loop through all the open workbooks and protect each workbook one by one using the same protect method.

'variable to use as a workbook
Dim wb As Workbook

'loop through all the open workbooks
For Each wb In Application.Workbooks

  'condition to check workbook name
  If wb.Name <> "PERSONAL.XLSB" Then

    'protect every workbook with a password
     wb.Protect "test123"

  End If

Next wb

End Sub

As I use the personal macro workbook, I have used an IF THEN ELSE statement to skip if the workbook name is “personal.xlsb”. Make sure to take care of that as well.

Protect a Closed Workbook

You can also protect and unprotect a workbook that is closed and saved on a location. Look at the following code where I have used the location of my desktop to use the file “test.xlsx”.

Sub vba_protect_workbook()

'declare varibale to use for the workbook
Dim wb As Workbook

'open the workbook that you want to protect
Set wb = Workbooks.Open("C:UsersDellDesktoptest.xlsx")

'turn off the screen-updating to _
done everything in the backend
Application.ScreenUpdating = False

'protect the workbook
wb.Protect

'close the workbook after protecting
wb.Close SaveChanges = True 'turn off the screen-updating
Application.ScreenUpdating = True

End Sub

The above code used Workbook.Open the method to open the workbook that you want to protect and then disable the screen updating to work in the backend.

Protect All the Closed Workbooks from a Folder

The following code loops through all the workbooks that are there in the folder that you specified as the path. And then open each workbook, protect it, and close it one by one.

Sub vba_protect_all_the_workbooks()

'declare variables
    Dim wb As Workbook
    Dim myPath As String
    Dim myFile As String
    Dim myExtension As String
    Dim FldrPicker As FileDialog

'optimize macro
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

'specify the folder where workbooks are saved
    myPath = "C:UsersDellDesktopfolder"

'jump to reset setting if there's an error
    On Error GoTo ResetSettings

'target file fxtension (must include wildcard "*")
    myExtension = "*.xls*"

'target path
    myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
    Do While myFile <> ""

        'Set variable equal to opened workbook
            Set wb = Workbooks.Open(Filename:=myPath & myFile)

        'protect workbook
            wb.Protect

        'Save and Close Workbook
            wb.Close SaveChanges:=True

        'Get next file name
            myFile = Dir

    Loop

'Message Box when task is completed
  MsgBox "Done!"

'reset setting that were change for optimization
ResetSettings:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub