ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

VBA: How to Password Protect all Sheets

Updated on November 2, 2012

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

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    Click to Rate This Article