Solved: Conditional Protection (locking) of a Row

[K. Georgiadis]

I have a pair of macros linked to option boxes, returning a value of 1 or 2 in linked cell G4. What is the best (simplest) way to:


lock cells c11:k11 if cell G4 reads 1
unlock cells c11:k11 if cell G4 reads 2?

[Jacob Hilderbrand]

Give this a try.

Option Explicit

Private Sub Worksheet_Calculate()

Dim Pass As String

Pass = "MyPassword"
ActiveSheet.Unprotect Password:=Pass
If Range("G4").Value = 1 Then
Range("C11:K11").Locked = True
ElseIf Range("G4").Value = 2 Then
Range("C11:K11").Locked = True
End If
ActiveSheet.Protect Password:=Pass, Contents:=True, Scenarios:=True, DrawingObjects:=True

End Sub


Also add a formula that will force this to trigger. So if G4 is the linked cell, in any other cell put =G4.

[K. Georgiadis]

Even I can see that this is going to work. Thanks!

[Jacob Hilderbrand]

You're Welcome :beerchug:

Take Care