Set cell protection on/off based on other cell value for multiple sheets

  • Ok I have ran out of options. I'm trying to create a Macro that will let me do the following.

    If I5:I24 equal "x", then K5:K24 are unlocked

    If I5:I24 is blank or anything else, then K5:K24 are locked.

    I don't need the entire K range locked if just I5 is blank or anything else. I need I5 to correspond with K5, I6 to correspond with K6, and so on...

    In addition to this, I need this same macro to work on seven tabs in the workbook. SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY

    In addition, the sheets have to be protected as well.

    This is what I have so far (if it's not in code I'm sorry this is my first post)

    This only works for I5 and K5 cells. I can't figure out how to do it for an entire range or on multiple sheets.

    Any help would be GREATLY appreciated.

  • Re: Set cell protection on/off based on other cell value for multiple sheets

    Hello betapaul,

    Welcome to Ozgrid.

    Be advised that all macro code is absolutely subject to the user choosing to enable macros when prompted upon opening the workbook. If the use opts not (or forgets) to enable macros then all code is defeated.

    You might consider using Data Validation to lock the cell unit the appropriate entry is made.

    This requires the use of a helper column, which can be hidden.
    Cells in the column would use this formula: =OR(I5={"X","x"})
    Which will return either TRUE or FALSE

    Say we use column-Z as the helper.

    In the cell you want to protect, use Data Validation, Custom option and this formula

    Set the Data Validation Error Alert Style to Stop and create a custom message to show the user.

    This bit of code goes into the Thisworkbook module

    Code for the worksheets

    Note: change the password to suit, or remove it (it is optional)

  • Re: Set cell protection on/off based on other cell value for multiple sheets

    I can't use data validation because I already am in K5:K24.

    I tried this code and it's just not working. It works for I5 in relation to K5, but not for the cells going down the spreadsheet.

    I believe the term I've seen used is "array" when discussing what I'm trying to do.

    If I5 is blank, then K5 is LOCKED.
    If I5 has "x" or "X", then K5 is unlocked.

    If I6 is blank, then K6 is LOCKED.
    If I6 has "x" or "X", then K6 is unlocked.

    Etc, etc, all the way to I24 and K24

    The tabs at the bottom are the days of the week and I need it to work on all tabs exactly like this.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!