MrExcel.com - Your One Stop for Excel Tips & Solutions

COnditional Cell locking, Tom Urtis

Posted by Stan on January 18, 2002 10:58 AM

Tom
Thanks a bunch for your reply, but I need further advice if possible.
For the question below, I need this to occur for multiple rows, so I need to have 4 COLUMNS where, for each row, entry in one cell will disallow entry into any of the other 3 cells (I assumed that the only way to do this was to trigger protection for each of the 3 remaining cells.)
I would like to know how to disable the visual error prompt when an attempt is made to access a protected cell.
Finally, if for a given row, one cell is used and the other 3 are protected, I would like to know how to unprotect those three protected cells if the entry in the one allowed is removed (basically setting the 4 cells back to their original state).
Quite a list, I know, but I can't believe that I am the only one who would have a need for this. Any help is appreciated, and again, thanks for the help so far.


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
three cells to prevent data from being entered any of those three remaining, blank cells.

Re: COnditional Cell locking, Tom Urtis

Posted by Tom Urtis on January 20, 2002 9:59 AM
A few questions:

(1) What are the columns exactly? I guessed A:D in my original reply but we might as well do the job for real so you won't have to adjust it.

(2) Why do you not want an error message to alert the user that they are trying to access a cell that is protected? You will have users pounding on their computers and keyboards in frustration when trying in vain to access a protected cell, not knowing why what they are attempting isn't working.

(3)Does "if the entry in the one allowed is removed" mean if the cell's contents are deleted (resulting in a blank cell), or undone, or made to be a certain value (such as "0"), or what?

Thanks.

Tom Urtis

Posted by Tom Urtis on January 20, 2002 9:59 PM

To get a jump on things...

See if this is closer to what you want. Not sure where I'll be on Monday. Right click on sheet tab, left click on View Code, and paste this in. Note, it is for range A2:D50, with the password as "PASSWORD", so modify as needed.


Private Sub Worksheet_Change(ByVal Target As Range)

'All conditions are for range A2:D50. Modify as needed

'Condition 1, if cell in column A is changed to anything except ""
If Target.Column = 1 And (Target.Row > 1 And Target.Row <= 50) Then
If Target.Value <> "" Then
'Unprotect sheet
ActiveSheet.Unprotect ("PASSWORD")
'Unlock columns A:D of that row
Range(Target, Target.Offset(, 3)).Locked = False
'Lock the 3 columns in that row not belonging to the target (B, C, D)
Range(Target.Offset(, 1), Target.Offset(, 3)).Locked = True
'Reprotect sheet
ActiveSheet.Protect ("PASSWORD")
Else
'If the target cell was changed to a "" value
'Unprotect sheet
ActiveSheet.Unprotect ("PASSWORD")
'Unlock columns A:D of that row to allow any cell to be accessed.
Range(Target, Target.Offset(, 3)).Locked = False
'Reprotect sheet
ActiveSheet.Protect ("PASSWORD")
End If

'Condition 2, if cell in column B is changed to anything except ""
ElseIf Target.Column = 2 And (Target.Row > 1 And Target.Row <= 50) Then
If Target.Value <> "" Then
'Unprotect sheet
ActiveSheet.Unprotect ("PASSWORD")
'Unlock columns A:D of that row
Range(Target.Offset(, -1), Target.Offset(, 2)).Locked = False
'Lock the 3 columns in that row not belonging to the target (A, C, D)
'First column A
Target.Offset(, -1).Locked = True
'Then columns C and D
Range(Target.Offset(, 1), Target.Offset(, 2)).Locked = True
'Reprotect sheet
ActiveSheet.Protect ("PASSWORD")
Else
'If the target cell was changed to a "" value
'Unprotect sheet
ActiveSheet.Unprotect ("PASSWORD")
'Unlock columns A:D of that row to allow any cell to be accessed.
Range(Target.Offset(, -1), Target.Offset(, 2)).Locked = False
'Reprotect sheet
ActiveSheet.Protect ("PASSWORD")
End If

'Condition 3, if cell in column C is changed to anything except ""
ElseIf Target.Column = 3 And (Target.Row > 1 And Target.Row <= 50) Then
If Target.Value <> "" Then
'Unprotect sheet
ActiveSheet.Unprotect ("PASSWORD")
'Unlock columns A:D of that row
Range(Target.Offset(, -2), Target.Offset(, 1)).Locked = False
'Lock the 3 columns in that row not belonging to the target (A, B, D)
'First columns A and B
Range(Target.Offset(, -2), Target.Offset(, -1)).Locked = True
'Then column D
Target.Offset(, 1).Locked = True
'Reprotect sheet
ActiveSheet.Protect ("PASSWORD")
Else
'If the target cell was changed to a "" value
'Unprotect sheet
ActiveSheet.Unprotect ("PASSWORD")
'Unlock columns A:D of that row to allow any cell to be accessed.
Range(Target.Offset(, -2), Target.Offset(, 1)).Locked = False
'Reprotect sheet
ActiveSheet.Protect ("PASSWORD")
End If

'Condition 4, if cell in column D is changed to anything except ""
ElseIf Target.Column = 4 And (Target.Row > 1 And Target.Row <= 50) Then
If Target.Value <> "" Then
'Unprotect sheet
ActiveSheet.Unprotect ("PASSWORD")
'Unlock columns A:D of that row
Range(Target, Target.Offset(, -3)).Locked = False
'Lock the 3 columns in that row not belonging to the target (A, B, C)
Range(Target.Offset(, -3), Target.Offset(, -1)).Locked = True
'Reprotect sheet
ActiveSheet.Protect ("PASSWORD")
Else
'If the target cell was changed to a "" value
'Unprotect sheet
ActiveSheet.Unprotect ("PASSWORD")
'Unlock columns A:D of that row to allow any cell to be accessed.
Range(Target, Target.Offset(, -3)).Locked = False
'Reprotect sheet
ActiveSheet.Protect ("PASSWORD")
End If

Else
Exit Sub

End If

End Sub

Any better?
Tom Urtis