Worksheets are objects in the worksheet collection of a workbook, and they have Protect and Unprotect methods. These methods determine the protected status of a worksheet as the name suggests. Both methods can also accept optional arguments. The first argument is the password. By setting a string into the parameter argument, you can lock your worksheets with a password. Below is a breakdown.
You can use this in two ways:
- Module
- Immediate Window
In the Module method, you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.
The Immediate Window method, on the other hand, is essentially a quick and dirty method where you can simply copy and paste the code into the Immediate Window, and press the Enter key to run it. Unfortunately, any code you use in the Immediate Window will not be saved. Also note that icons and keyboard shortcuts will not be available.
Protect a worksheet
Module Version:
Sub ProtectActiveWorksheet() ActiveSheet.Protect "pass" End Sub
Immediate Window version:
ActiveSheet.Protect "pass"
Unprotect a worksheet
Module Version:
Sub UnprotectActiveWorksheet() ActiveSheet.Unprotect "pass" End Sub
Immediate Window version:
ActiveSheet.Unprotect "pass"
Protect all worksheets
Module Version:
Sub ProtectAllWorksheets() Dim sh As Worksheet For Each sh In ActiveWorkbook.Worksheets sh.Protect "pass" Next sh End Sub
Immediate Window version:
For Each sh In ActiveWorkbook.Worksheets: sh.Protect "pass": Next sh
Unprotect all worksheets
Module Version:
Sub ProtectAllWorksheets() Dim sh As Worksheet For Each sh In ActiveWorkbook.Worksheets sh.Unprotect "pass" Next sh End Sub
Immediate Window version:
For Each sh In ActiveWorkbook.Worksheets: sh.Unprotect "pass": Next sh