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.
- Specify the workbook that you want to protect.
- Type and dot and select the protect method from the list or you can type “Protect”.
- Enter the password that you want to set.
- 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.
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.
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.
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:\Users\Dell\Desktop\test.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:\Users\Dell\Desktop\folder\" '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
More on VBA Workbooks
VBA Save Workbook | VBA Close Workbook | VBA Delete Workbook | VBA ThisWorkbook | VBA Rename Workbook | VBA Activate Workbook | VBA Combine Workbook | VBA Check IF a Workbook is Open | VBA Open Workbook | VBA Check IF an Excel Workbook Exists in a Folder| VBA Create New Workbook (Excel File)