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.
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
Download the Sample File
If you’d like to see all the code, you can download the sample file from the Worksheet Protection Selector page on my Contextures website. On that page, scroll down to the Download section, and you’ll see a link to the file.
The file contains macros, so you’ll have to enable them to test the code.