PDA

View Full Version : Solved: Conditional Protection (locking) of a Row



K. Georgiadis
03-28-2006, 08:10 PM
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
03-28-2006, 10:00 PM
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
03-29-2006, 06:05 AM
Even I can see that this is going to work. Thanks!

Jacob Hilderbrand
04-16-2006, 12:26 PM
You're Welcome :beerchug:

Take Care