- HubPages»
- Technology»
- Computers & Software»
- Computer Science & Programming»
- Programming Languages
VBA: How to Password Protect all Sheets
The Problem
As you begin to create advanced spreadsheets it will become necessary to apply protection, to keep end users or even yourself from making changes to the structural integrity of your project.
Macros that reference multiple sheets will often require you to protect and unprotect each sheet in your code. On large projects this can become quite a repetitive task.
The Solution
Create two macros that loops through each sheet in your workbook either protecting or unprotecting it. These macros can then be referenced at the beginning and end of each of your subroutines.
The Code
Sub ProtectAllSheets() Dim sh As Worksheet For Each sh In Sheets 'protect each sheet with "Test" as password sh.Protect "Test" Next sh End Sub Sub UnprotectAllSheets() Dim sh As Worksheet For Each sh In Sheets 'unprotect each sheet with "Test" as password sh.Unprotect "Test" Next sh End Sub Sub Example() 'sample macro unprotecting each sheet, clearing its contents, and restoring protection Call UnprotectAllSheets Dim sh As Worksheet For Each sh In Sheets sh.Cells.ClearContents Next sh Call ProtectAllSheets End Sub