Conditional locking of cells for individual rows
Dear Dave
Again thak you for your input.
It works just fine when the locking i controlled by an inputed i column B,
but what if I want it to be controled by the result of an formula i column B
instead? (column B will be lock at all times)
Hope you can help again.
"Dave Peterson" skrev:
Column B controls the "lockedness" of column A?
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'one cell at a time
If Target.Cells.Count 1 Then Exit Sub
'only in column B
If Intersect(Target, Me.Range("B:B")) Is Nothing Then Exit Sub
On Error GoTo ErrHandler:
Me.Unprotect
Target.Offset(0, -1).Locked = CBool(Target.Value = 1)
ErrHandler:
Me.Protect
Application.EnableEvents = True
End Sub
mthatt wrote:
I need to lock cells in a column based on a value in another column.
So if value en column B is "1" then cell in same row in column A will be
locked else unlocked.
I have tried this setup:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Address = "$A$1" Then
ActiveSheet.Unprotect
ActiveSheet.Cells.Locked = False
If Target.Value = "1" Then
Range("B1:b10").Cells.Locked = True
End If
ActiveSheet.Protect
End If
enditall:
Application.EnableEvents = True
End Sub
But if only locks based on the value in af fixed celle A$1$, and i cant seem
to find have to adjust it, so condition in column A and locking af cell in
column B work for each row individual.
I've seen in other topics on this board, that data validation can be used -
ived tried it, and it works so that you cannot change data i the cell, BUT
you can delete!! - so the data validation is not an option for me in this
case.
Can anyone help me?
--
Dave Peterson
|