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"
- 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.
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
Related Tutorials
- Copy an Excel File (Workbook) using VBA
- VBA Activate Workbook (Excel File)
- VBA Close Workbook (Excel File)
- VBA Combine Workbooks (Excel Files)
- VBA Create New Workbook (Excel File)
- VBA Delete Workbook (Excel File)
- VBA Open Workbook (Excel File)
- VBA Rename Workbook (Excel File)
- VBA Save Workbook (Excel File)