Results 1 to 9 of 9

Thread: Lock/Unlock specific and multiple worksheets

  1. #1
    Acolyte GTretick's Avatar
    Join Date
    Jul 2015
    Location
    Canada
    Posts
    37
    Articles
    0
    Excel Version
    365

    Lock/Unlock specific and multiple worksheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,123
    Articles
    0
    Excel Version
    365
    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.
    Attached Files Attached Files

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    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.
    Attached Files Attached Files

  4. #4
    Acolyte GTretick's Avatar
    Join Date
    Jul 2015
    Location
    Canada
    Posts
    37
    Articles
    0
    Excel Version
    365
    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.

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,123
    Articles
    0
    Excel Version
    365
    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.
    Last edited by p45cal; 2021-12-24 at 05:55 PM.

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by GTretick View Post
    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?

  7. #7
    Acolyte GTretick's Avatar
    Join Date
    Jul 2015
    Location
    Canada
    Posts
    37
    Articles
    0
    Excel Version
    365
    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.

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,123
    Articles
    0
    Excel Version
    365
    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.

  9. #9
    Acolyte GTretick's Avatar
    Join Date
    Jul 2015
    Location
    Canada
    Posts
    37
    Articles
    0
    Excel Version
    365
    Ahh. Yes that worked. I have shown this to my admin assistant and she is very happy! Thanks again.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •