Protect multiple sheets at once
This tutorial shows how to protect multiple sheets at once through the use of VBA
METHOD 1. Protect multiple sheets at once defined in the VBA code using VBA
VBA
Sub Protect_Multiple_Sheets()
'declare variables
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Sheets("Sheet1")
Dim ws2 = Sheets("Sheet2")
Dim ws2 = Sheets("Sheet2")
ws1.Protect Password:="Pword"
ws2.Protect Password:="Pword"
ws2.Protect Password:="Pword"
End Sub
ADJUSTABLE PARAMETERS
Password: Enter the password that you want to protect each specified sheet in the workbook with by changing "Pword" in the VBA code.
Worksheet Selection: Select the sheets that you want to protect by changing the sheet names "Sheet1" and "Sheet2" in the VBA code. You can protect more sheets by using same code as above, only assigning the Protect function and password to a different sheet.
Password: Enter the password that you want to protect each specified sheet in the workbook with by changing "Pword" in the VBA code.
Worksheet Selection: Select the sheets that you want to protect by changing the sheet names "Sheet1" and "Sheet2" in the VBA code. You can protect more sheets by using same code as above, only assigning the Protect function and password to a different sheet.
ADDITIONAL NOTES
Note 1: Using this VBA code you need to list all of the sheets that you want to protect directly in the VBA code.
Note 1: Using this VBA code you need to list all of the sheets that you want to protect directly in the VBA code.
METHOD 2. Protect multiple sheets at once sourced from a list using VBA
VBA
Sub Protect_Multiple_Sheets()
'declare variables
Dim wsp As Worksheet
Dim ws As Worksheet
Dim wsname As String
Dim wsp As Worksheet
Dim ws As Worksheet
Dim wsname As String
Set wsp = Sheets("Parameters")
'loop through each cell in the range that contains the names of the sheets that you want to protect
On Error Resume Next
For x = 1 To 2
wsname = wsp.Range("A" & x)
Set ws = Sheets(wsname)
ws.Protect Password:="Pword"
On Error Resume Next
For x = 1 To 2
wsname = wsp.Range("A" & x)
Set ws = Sheets(wsname)
ws.Protect Password:="Pword"
Next x
End Sub
ADJUSTABLE PARAMETERS
Password: Enter the password that you want to protect each specified sheet in the workbook with by changing "Pword" in the VBA code.
Parameters Worksheet Selection: Select the worksheet that list the names of the sheets that you want to protect by changing the "Parameters" sheet name in the VBA code.
Range of Names: In this example the names of the sheets to be protected are captured in range (A1:A2) in the Parameters worksheet. To change the range of where you capture the names of the sheets to protect, change the column reference "A" and the values that are assigned to x, which are 1 to 2, in the VBA code.
Password: Enter the password that you want to protect each specified sheet in the workbook with by changing "Pword" in the VBA code.
Parameters Worksheet Selection: Select the worksheet that list the names of the sheets that you want to protect by changing the "Parameters" sheet name in the VBA code.
Range of Names: In this example the names of the sheets to be protected are captured in range (A1:A2) in the Parameters worksheet. To change the range of where you capture the names of the sheets to protect, change the column reference "A" and the values that are assigned to x, which are 1 to 2, in the VBA code.
EXPLANATION
This tutorial shows how to password protect multiple sheets at once by using VBA.
This tutorial shows how to password protect multiple sheets at once by using VBA.
The first method allows you to directly insert the names of the sheets that you want to protect in the VBA code. The second method sources the names of the sheets that you want to protect from a list in a worksheet, and loops through each cell in the range to apply a password protection.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Protect a single sheet | How to protect a single sheet in a workbook using Excel or VBA | |
| Unprotect a single sheet | How to unprotect a single sheet in a workbook using Excel or VBA | |
| Protect all sheets at once | How to protect all sheets in a single workbook at once through the use of VBA | |
| Unprotect all sheets at once | How to unprotect all protected sheets in a single workbook at once through the use of VBA |