Can't get code to work when exiting cell

[rob nobel]

The part code (following), seems to work OK, but only if I click the green
tick by the formula bar, as when I press enter, or select another cell to
exit the cell where I've entered an X, it doesn't work.
I realise that this is because the active cell is no longer the one where I
typed an X but I don't know how to get this procedure to do this.

What I've tried to do (in an awkward way) is....
1. Type an X into Column N and make all the cells (in that row) in columns
G:N and P:R, locked.
2. Make those cells unlocked if the x is deleted.
3. What I'd also like is to have the procedure work whether it is an upper
or lowercase x.
Can someone please help with this?

Furthermore, is it possible to have separate Worksheet_Change events for
different procedures on the same sheet as I'm trying to do quite a number of
things on the same sheet and it all becomes rather confusing doing it all
under the one Worksheet_Change event. ( I am also using a
Worksheet_SelectionChange event on this sheet.)

Rob

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("N26:N1525")) Is Nothing Then GoTo
NextTest Else GoTo BalCol

'Some other code here

BalCol:
If ActiveCell = "X" Then
With ActiveCell
.Offset(0, -7).Resize(1, 7).Locked = True
.Offset(0, 2).Resize(1, 3).Locked = True
End With
End If
If ActiveCell = "" Then
With ActiveCell
.Offset(0, -7).Resize(1, 7).Locked = False
.Offset(0, 2).Resize(1, 3).Locked = False
End With
End If
End Sub

 

[BrianB]

I cannot reproduce your problem behaviour (XL97). Th Change event fire
when the contents of a cell are changed. There may be other settings i
your sheet affecting this. I suggest you start again with a clea
workbook and put the following code into one of the sheet modules, an
change a few cell values at random :-

'----------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
MsgBox (Target.Address)
End Sub
'-------------------------------------------------

This will ensure that this is working and give an idea how you can se
up different actions depending on the address of the changed cell

 

[rob nobel]

Thanks for that Brian. That MsgBox suggestion did the trick as it did show
there was no problem and prompted me to do ...
If Target.Value = "X"
instead of ...
If ActiveCell = "X"

There was another question I needed help with.....

Is it possible to have separate Worksheet_Change events for
different procedures on the same sheet as I'm trying to do quite a number of
things on the same sheet and it all becomes rather confusing doing it all
under the one Worksheet_Change event. ( I am also using a
Worksheet_SelectionChange event on this sheet.)

Rob

 

[Dave Peterson]

If you want to use the cell you just changed, then change all those activecell
references to Target.

Something like:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("N26:N1525")) Is Nothing Then
GoTo nexttest
Else
GoTo BalCol
End If

'Some other code here

BalCol:
With Target
If UCase(.Value) = "X" Then
.Offset(0, -7).Resize(1, 7).Locked = True
.Offset(0, 2).Resize(1, 3).Locked = True
ElseIf .Value = "" Then
.Offset(0, -7).Resize(1, 7).Locked = False
.Offset(0, 2).Resize(1, 3).Locked = False
End If
End With

nexttest:

End Sub

But if you could live with an empty/not empty test, this is less typing:

With Target
.Offset(0, -7).Resize(1, 7).Locked = Not (CBool(IsEmpty(.Value)))
.Offset(0, 2).Resize(1, 3).Locked = Not (CBool(IsEmpty(.Value)))
End With

 

[rob nobel]

Yes Dave, I actually figured out that that was the problem. But thanks for
suggesting it though. Thanks too for the If UCase(.Value) = "X", as I did
get it working but my bit was a bit long winded.
I also appreciate your "less typing" suggestion but I might stick with the
other as it seems to be a little easier to understand.

Rob