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

betapaul · Apr 23rd 2011

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)

Code

/Private Sub Worksheet_Change(ByVal Target As Range)


Select Case Me.Range("I5")
       Case "X", "x"
       With Me
           .Unprotect
           .Range("K5").Locked = False
           .Range("I5").Locked = False
           .Protect
       End With
       Case Else
       With Me
           .Unprotect
           .Range("K5").Locked = True
           .Range("I5").Locked = False
           .Protect
       End With
   End Select

Display More

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.

AAE · Apr 24th 2011

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
=Z5=TRUE

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

Option Explicit


Private Sub Workbook_Open()


    Dim ws As Worksheet
    
    For Each ws In Sheets(Array("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday""Friday", "Saturday"))
        ws.Protect Password:="Mypassword", userinterfaceonly:=True
    Next ws


End Sub

Display More

Code for the worksheets

Code

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Cells.Count > 1 Then Exit Sub


    If Not Intersect(Target, Range("I5:I24")) Is Nothing Then 'user is changing a cell in the target range
        With Target
            Select Case .Value
                Case "X", "x"
                    Range("K" & Target.Row).Locked = False
                
                Case ""
                    Range("K" & Target.Row).Locked = True
            End Select
            
        End With
    End If


End Sub

Display More

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

betapaul · May 3rd 2011

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.

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

Participate now!

Share