Conditional locking of cells for individual rows
 

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?

Conditional locking of cells for individual rows
 

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

Conditional locking of cells for individual rows
 

Thanks you very much Dave.

I was way off on a wrong track and you saved me a lot af time.

mthat
"mthatt" skrev:

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?

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

Conditional locking of cells for individual rows
 

Option Explicit
Private Sub Worksheet_Calculate()

Dim myRngToCheck As Range
Dim myCell As Range

Set myRngToCheck = Nothing
On Error Resume Next
Set myRngToCheck = Intersect(Me.Range("b:b"), Me.UsedRange)
On Error GoTo 0

If myRngToCheck Is Nothing Then
Exit Sub
End If

Me.Unprotect
On Error GoTo ErrHandler:
For Each myCell In myRngToCheck.Cells
myCell.Offset(0, -1).Locked = CBool(myCell.Value = 1)
Next myCell

ErrHandler:
Me.Protect
Application.EnableEvents = True
End Sub



mthatt wrote:

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


--

Dave Peterson