PDA

View Full Version : Lock active cell based on value in above cell



rcripes
09-18-2007, 08:57 AM
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
09-18-2007, 10:04 AM
With Activecell
.Locked = (.Offset(-1,0).Value = 0)
End With

rcripes
09-18-2007, 10:27 AM
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
09-18-2007, 10:30 AM
You need a worksheet change event for that. Right click your worksheet tab and select View Code, paste this in the code pane...

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

HTH

rcripes
09-18-2007, 11:17 AM
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
09-18-2007, 11:20 AM
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

mdmackillop
09-18-2007, 11:30 AM
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

rcripes
09-18-2007, 02:58 PM
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
09-18-2007, 02:59 PM
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
09-19-2007, 03:46 PM
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?