Lock cell when another cell populated

[lesley burlingham]

I would like cell (B47:C47) to lock once a value >1 is entered into Cell N47. I already have a button on the page to lock and unlock the worksheet.

 

[YasserKhalil]

Hi lesley
Try this code in worksheet_Change

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$N$47" Then
        ActiveSheet.Unprotect 1
            If Target.Value > 1 Then
                Range("B47:C47").Locked = True
            Else
                Range("B47:C47").Locked = False
            End If
        ActiveSheet.Protect 1
    End If
End Sub

 

[Deepak]

Just little version of YasserKhalil

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$N$47" Then
        ActiveSheet.Unprotect 1
            Range("B47:C47").Locked = Target.Value > 1
        ActiveSheet.Protect 1
    End If
End Sub

 

[lesley burlingham]

Thank you very much both of you. I have multiple ranges which need to be locked will this be a case of just adding it to the list.

  If Target.Address = "$N$47" Then
  ActiveSheet.Unprotect 1
  Range("B47:C51").Locked = Target.Value > 1
  Range("E7:O9").Locked = Target.Value > 1
  ActiveSheet.Protect 1
  End If

 

[YasserKhalil]

So try this modification

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$N$47" Then
        Dim Rng As Range
        Set Rng = Union(Range("B47:C51"), Range("E7:O9"))
        ActiveSheet.Unprotect 1
            Rng.Locked = Target.Value > 1
        ActiveSheet.Protect 1
    End If
End Sub

You can add more ranges in this line

Set Rng = Union(Range("B47:C51"), Range("E7:O9"))

 

[lesley burlingham]

Thanks for the reply, I am getting an error when, the debug is highlighting 'Rng.Locked= target.value >1' when I run the query.

'Unable to set the locked property of the range class'

 

[Deepak]

Thanks for the reply, I am getting an error when, the debug is highlighting 'Rng.Locked= target.value >1' when I run the query.

'Unable to set the locked property of the range class'

Just tested with YasserKhalil test book & found working.

You might did something wrong there!!!

 

[lesley burlingham]

I tested it on the example sent also and it works find, I have copied the code to my spreadsheet and it errors.

 

[Deepak]

I tested it on the example sent also and it works find, I have copied the code to my spreadsheet and it errors.

Sample xl pls.