Let Users Lock or Unlock an Excel Workbook

Some people like an Excel workbook that’s locked down, so they can’t accidentally mess anything up. They just want to go to the data entry section, put in their data, and get out alive.

Other people hate Excel workbooks that are protected. Maybe they know a bit more about Excel, and are comfortable making changes. Or, they’ve been assigned to manage a workbook, and don’t want to bother with worksheet protection, because it slows them down.

Give the Users Control

One of my clients has plants all over the world, and we’ve made a similar data collection workbook for each plant. On the last sheet of the workbook, I’ve added a drop down list, where the user can select TRUE or FALSE.

If the setting changed to FALSE, a macro runs, to unprotect all the worksheets. If the setting is changed to TRUE, all the sheets are protected.

The TRUE/FALSE option is a quick and easy way for users to control the workbook settings, and seems to be working well.

The Code

There’s code on the worksheet module that runs when the Lock cell’s value is changed. To see the code, right-click on the sheet tab where the drop down list is located, and click View Code.

Here’s the bit of code that checks the Lock cell, and protects or unprotects the sheets. In the sample file, there is the full code, and another example that protects or unprotects with a password.

If Target.Address = wsListsAll.Range(“Lock”).Address Then
For Each ws In ThisWorkbook.Worksheets
If bLock = True Then
ws.Protect
Else
ws.Unprotect
End If
Next ws
End If