Conditional Locking of Cells

[Simmo1313]

Hi all,

I have a range of empty cells in columns A,B & C.

If a value is entered in any of the cells A1, B1 or C1 i want to lock the other two cells to prevent data entry.

This will then be the same for rows 1 through to 4 as shown in the table below..


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[TD]locked[/TD]
[TD]locked[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]locked[/TD]
[TD]8[/TD]
[TD]locked[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]locked[/TD]
[TD]locked[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]locked[/TD]
[TD]locked[/TD]
[TD]77[/TD]
[/TR]
</tbody>[/TABLE]

Can this be done without VBA?

I would like to avoid that where possible as i'm not very well schooled in that area.

Any help would be appreciated.

 

[Michael M]

Because your data vs locked cells will vary, I would imagine you will need VBA.
AND the sheet will need protecting / unprotecting each time you want input data.
Also, what happens when you need to change a cell in a row that has already had data placed in it?
Will you clear the row manually, or does the VBA have to do it ??

 

[Simmo1313]

Michael,

Thanks, I was afraid VBA would be involved.

I would clear the value that was input into one of the cells manually and that would reset the locking of the other cells.

 

[Michael M]

Untested, but if you manually clear cells 1 to 3 on an row, then input data in any one of them, this should do it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, Cell As Range
    Set Rng = Range("A" & Target.Row & ":C" & Target.Row)
 If WorksheetFunction.Count(Rng) = 0 Then Exit Sub
    For Each Cell In Rng
        If Cell.Value <> "" Then Rng.Locked = True
    Next
     ActiveSheet.Protect ("1")
End Sub