Lock active cell based on value in above cell

[rcripes]

Hello. I'm new here, and need an Excel VBA macro that locks the active cell I am in based on whether the value in the cell above is 0. That is, If cellabove = 0, then active cell is locked, but if cellabove => 1 then not locked. Thanks!

[Bob Phillips]

[vba]

With Activecell
.Locked = (.Offset(-1,0).Value = 0)
End With
[/vba]

[rcripes]

I have pasted the following into the module, but it doesn't seem to want to run automatically. Any suggestions? Thanks.

Sub LockActiveCell()

With ActiveCell
.Locked = (.Offset(-1, 0).Value = 0)
End With

End Sub

[Zack Barresse]

You need a worksheet change event for that. Right click your worksheet tab and select View Code, paste this in the code pane...

[vba]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Target.Offset(1, 0).Locked = (Target.Value = 0)
End Sub[/vba]

HTH

[rcripes]

Sorry, but it still doesn't want to work. When I try to input any integer (I'm using 1 to try it out) in the active cell, I get error message: "Unable to set the locked property of the range class", then on to debug which highlights:

Target.Offset(1, 0).Locked = (Target.Value = 0)

I am just wanting to stop user input in the active cell if the cell above is 0. Any other number >0 in the cell above allows user input of any integer =>1 in the active cell. Thanks.

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Me.Unprotect
Target.Offset(1, 0).Locked = (Target.Value = 0)
Me.Protect
End Sub
[/vba]

[mdmackillop]

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = 0 Then
ActiveSheet.Unprotect
Target.Offset(1, 0).Locked = True
ActiveSheet.Protect
End If
End Sub
[/VBA]

[rcripes]

Still no go. Perhaps I should mention that I'm using Excel 2000 . . .
I'm thinking that evaluating the cell above the active cell would be offset(-1,0), right?

[rcripes]

Still no go. Perhaps I should mention that I'm using Excel 2000 . . .
I'm thinking that evaluating the cell above the active cell would be offset(-1,0), right?

[Zack Barresse]

Yes, you are correct about the offset, with the row value of -1 being the row directly above the cell in question (i.e. Target). When you use xld's code, where does it error out at and what is the error message?