Contextures

Worksheet Protection Selector

Quickly turn worksheet protection on or off, by selecting TRUE or FALSE from a drop down list. When the selection changes, a macro runs, and protects or unprotects all worksheets.

Protection Selector Intro

If an Excel workbook has all its sheets protected, that can help prevent the data and formulas from being accidentally damaged or overwritten. Some sections can be left unlocked, for new data to be entered, and other sections can have their cells locked.

However, you might not want the sheets locked all the time, because that can slow you down, if you're making extensive changes to a workbook.

To quickly protect or unprotect all the sheets in a workbook, you can add a drop down list with TRUE and FALSE as the options. Make a selection in that list, and a macro will run to change the sheet protection.

select true or false

Add a Drop Down List

The first step is to add a drop down list, somewhere in the workbook. In this example, the drop down list will be added to a sheet named Admin.

To add the drop down list:

  1. In cell B2, type a heading: Lock
  2. Select cell B3, and on the Ribbon's Data tab, click Data Validation.
  3. From the Allow drop-down list, choose List
  4. Click in the Source box, and type: TRUE, FALSE
  5. data validation settings

  6. Click OK to close the Data Validation dialog box.
  7. (Optional) Change the fill colour in cell B3, and add a border
  8. Click the drop down arrow that appears on cell B3, and select TRUE or FALSE

NOTE: Nothing will happen, because the code hasn't been added to the workbook yet.

select true or false

Name the Cell

Next, you'll name the cell that contains the drop down list. That name will be used in the macro.

To name the cell:

  1. Select the cell that contains the drop down list -- cell B3
  2. Click in the Name Box, to the left of the Formula Bar
  3. Type a one-word name for the cell -- Lock -- then press Enter, to complete the name

name the cell

Add the Code

The next step is to add code to the Admin worksheet, and it will run when the selection changes in cell B3. That cell is named Lock, and that name is used in the code. This will prevent problems that could occur if you used "B3" as the location, and someone moved that cell, or inserted a row above it.

How to Add the Code to Your Workbook

Copy one version of the code below. Then, right-click the Admin sheet tab and click View Code. Paste the code in the code window, where the cursor is flashing. (Note: If there is alread a Worksheet Change event, you will have to incorporate this new code with the existing code.)

There are two versions of the code:

Code for Protection With a Password

Use this code if the sheet are protected with a password. Substitute your password for the "MYPWD" password in the code. Also, in the code, change the worksheet name to match the name of the sheet in your workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim rLock As Range
Dim bLock As Boolean
Dim strPwd As String

Set rLock = Range("Lock")
bLock = rLock.Value
strPwd = "MYPWD"

Application.EnableEvents = False
Application.ScreenUpdating = False

If Target.Address = rLock.Address Then
  For Each ws In ThisWorkbook.Worksheets
    If bLock = True Then
      ws.Protect Password:=strPwd
    Else
      ws.Unprotect Password:=strPwd
    End If
  Next ws
End If

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Code for Protection With NO Password

Use this code if the sheet are protected, but there is NO password. In the code, change the worksheet name to match the name of the sheet in your workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim rLock As Range
Dim bLock As Boolean

Set rLock = Range("Lock")
bLock = rLock.Value

Application.EnableEvents = False
Application.ScreenUpdating = False

If Target.Address = rLock.Address Then
  For Each ws In ThisWorkbook.Worksheets
    If bLock = True Then
      ws.Protect
    Else
      ws.Unprotect
    End If
  Next ws
End If

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Download the Sample File

To test the code, you can download the Protection Selector sample file. The zipped file is in xlsm format, and contains macros. The example for password protection is on the AdminPwd sheet.

More Tutorials

Worksheet Tips

Worksheet VBA

Hide Sheets Based on Tab Colour

Worksheet AutoFilter VBA

Copy VBA to Sheet

VBA - Get Started

Search Contextures Sites

 

Excel Tools Add-in

Free Pivot Table Tools

 

 

 

 

Excel Data Entry Popup List

 

 

 

 

Last updated: August 27, 2016 5:43 PM
Contextures RSS Feed