Conditional cell locking....
Posted by Stan on January 16, 2002 12:02 PM
This is probably an easy one, but it doesn't matter if its easy if I can't do it!
I wish to conditionally lock cells... for example, if I have four cells next to each other in a row, and data is entered into one of those cells, I want to lock the other three cells to prevent data from being entered any of those three remaining, blank cells.
I'd appreciate any help. Thanks!
Posted by Tom Urtis on January 16, 2002 1:02 PM
Here's one way to do it
Stan,
Right click on your sheet tab, left click on View Code, and paste this in:
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$A$1"
ActiveSheet.Unprotect ("PASSWORD")
Range("$B$1:$D$1").Locked = True
ActiveSheet.Protect ("PASSWORD")
Case "$B$1"
ActiveSheet.Unprotect ("PASSWORD")
Range("$A$1:$D$1").Locked = True
Range("$B$1").Locked = False
ActiveSheet.Protect ("PASSWORD")
Case "$C$1"
ActiveSheet.Unprotect ("PASSWORD")
Range("$A$1:$D$1").Locked = True
Range("$C$1").Locked = False
ActiveSheet.Protect ("PASSWORD")
Case "$D$1"
ActiveSheet.Unprotect ("PASSWORD")
Range("$A$1:$C$1").Locked = True
ActiveSheet.Protect ("PASSWORD")
Case Else
Exit Sub
End Select
End Sub
It assumes the 4-cell range of interest is unlocked to begin with (that's logical), and that you've unlocked other cells on the worksheet already, having nothing to do with these 4 cells, that you will also want to enter data into without worrying about what happens with the 4 cell range.
Modify the cell addresses and password as needed.
Tom Urtis
