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.

LockFalse

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

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.

_______________________

Save

You may also like...

3 Responses

  1. Jayson says:

    On one client I put an option to lock/unlock sheets in the add-in menu. They wanted to be able to lock the file down to prevent mistakes, but also wanted some freedom.

  2. Daniel says:

    Great post! Thanks for the information. This is going to help us a bunch.

  3. Dave says:

    WOW just what I’m looking for… but as a beginning beginner, I have a few questions.
    1. The protection on the workbook I use has password protection on each sheet. Will this work with that?

    2. When I looked at locksheet.xlsm and went to Tools-Macros there was nothing there, but when I opened the VBA editor it was there.

    3. Do I just copy and paste this into a new macro?

    4. How do I set up the True/False sheet to make it work?

Leave a Reply to Jayson Cancel reply

Your email address will not be published. Required fields are marked *