Lock/Unlock specific and multiple worksheets

**GTretick** · 2021-12-17, 07:40 PM

I work in an Excel file that has multiple worksheets and can have additional ones added daily.

We lock off a worksheet when it is completed but sometimes need to go back to make amendments.

It can be very time consuming to go back and unlock 30 sheets make the change and then lock them back up again. The file/info menu option doesn't help that area too much.

What I'd like is to have a worksheet called say "Lock Control" that lists out all the worksheets currently in the workbook (as worksheets are added the list will expand) then the user can mark an "x" next to the worksheet to determine what state they want it in (locked or unlocked - or stay the same if no "x" is entered) then go through the workbook and change the lock status as required when we push a "Go" button. A password can be entered in an input area (or popup box if you want to be fancy) to use in order to set the status.

I've tried looking at this online and the answers don't seem to get me there. Hopefully someone with better VBA than me can recommend something.

I've attached a basic workbook to illustrate what a basic workbook might look like.

Thanks.

**p45cal** · 2021-12-19, 05:12 PM

In the attached, 2 buttons:
1. Check status quo, will update columns A:B with sheet names and lock status of each sheet.
2. A button to change the lock status of the sheets according to column B.

It's rudimentary, no passwords used - I'll leave that to you.
All code in the Lock Control sheet's own code-module.

**Bob Phillips** · 2021-12-22, 08:00 PM

Here is an alternate version.

The list regenerates each time you activate the control sheet, in case a user manually protects or unprotects a sheet (if they protect it with anything other than the control password, we are done for).
In my code, I leave you to protect/unprotect the sheet by putting an X or blank in the cell in. column B alongside the targeted sheet name.
I have also added hyperlinks so that you can click on the sheet name and jump to that sheet from the control sheet.
I also trap a change to the control password, and if it is changed, I go and change all of the protected sheets to that passowrd.

**GTretick** · 2021-12-24, 05:27 PM

Thanks for getting me started.

p45scal. The sheet works like I imagined however I don't have the knowledge to get to the next step of entering a password.

Bob. The sheet will lock/unlock on entering an X in the column however i can't seem to get it to do so consistently. Is there a method of entering that it prefers so that the lock/unlock effect is triggered? most of the time i try to toggle this it doesn't seem to happen however once in a while it does but I'm not sure what I did differently.

**p45cal** · 2021-12-24, 05:49 PM

Change:

Code:

If Not Sheets(cll.Value).ProtectContents Then Sheets(cll.Value).Protect

to:

Code:

If Not Sheets(cll.Value).ProtectContents Then Sheets(cll.Value).Protect "Abc123!"

and the same with the Unprotect line.
You should unlock all sheets first, then when you lock them again they will all be locked with the password.

**Bob Phillips** · 2021-12-27, 04:02 PM

Originally Posted by GTretick

Bob. The sheet will lock/unlock on entering an X in the column however i can't seem to get it to do so consistently. Is there a method of entering that it prefers so that the lock/unlock effect is triggered? most of the time i try to toggle this it doesn't seem to happen however once in a while it does but I'm not sure what I did differently.

Need a bit more detail. Are you trying to lock/unlock more than one sheet at a time, selecting multiople cells on control? If not, can you give a series steps that will show the problem?

**GTretick** · 2022-01-04, 07:35 PM

p45scal: I've got it going so that I can enter a password in an input box and it does what it is supposed to do. I thought it was perfect until I tested it on some real data. Turns out if I name a sheet as a mock date i.e. "Jan 4" (without the quotes) I believe it is reading it as text within the tab name but entering it as a date in the Lock control list. This produces a Subscript out of range error when I run the Change Locks function. I need to somehow enter the dates in column A (in Lock Control) as text. formatting the column in text only produces 44565 as the date. I could avoid dates in the naming but this is the primary way we name our tabs and the people using this sheet would probably never learn to adapt consistently.

Bob: I am trying to lock/unlock multiple sheets at once. Not sure what you mean with the "on control" comment however since the first answer from p45scal more or less gets me to where I want to be I shouldn't waste your time any more.

Thanks to you both for your assistance to this point.

**p45cal** · 2022-01-04, 09:34 PM

Change the format of column A to Text. Yes, at first it will produce a number, but if you click Check status quo again the name of the sheet should appear. Everything worked OK after that here.