Worksheet_Change not changing

[Sandy]

Why does this code not run when the worksheet changes?
It will run correctly if I change the first line to:-
"Private Sub Work()" and then run it manually.
Sandy

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False

For Each mycell In Range("C14:K14,M14:U14")
With mycell
If mycell.Offset(-5).Value = 3 Then
.Value = "Miss"
ElseIf mycell.Offset(-5).Value <> 3 Then
mycell.Value = "Hit"
End If
End With
Next mycell

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

 

[KLZA]

Why does this code not run when the worksheet changes?
It will run correctly if I change the first line to:-
"Private Sub Work()" and then run it manually.
Sandy

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False

For Each mycell In Range("C14:K14,M14:U14")
With mycell
If mycell.Offset(-5).Value = 3 Then
.Value = "Miss"
ElseIf mycell.Offset(-5).Value <> 3 Then
mycell.Value = "Hit"
End If
End With
Next mycell

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Try putting the code in your worksheet rather than thisworkbook...

 

[Zone]

Sandy,
1. Do you have the subroutine in the worksheet module for the worksheet
that's being changed?
2. Are events turned on?
3. How are you making a change to the worksheet?
James

 

[JE McGimpsey]

Does it fire after you close XL and reopen the file?

If an error occurred before events were re-enabled, none of your event
macros will fire. You may want to use this technique instead:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrHandler
Application.ScreenUpdating = False
Application.EnableEvents = False

For Each mycell In Range("C14:K14,M14:U14")
With mycell
If mycell.Offset(-5).Value = 3 Then
.Value = "Miss"
ElseIf mycell.Offset(-5).Value <> 3 Then
mycell.Value = "Hit"
End If
End With
Next mycell

ErrHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Otherwise, are you sure it's not running (i.e., set a breakpoint at the
first line and initiate a manual change)?

 

[Sandy]

James
1. The sub is in "Sheet1(Sheet1)" which is the sheet the data is on.
2. Events turned on ????
3. ("C9:K9,M9:U9") contains integers varying between 2 and 6. I change say a
4 to a 3 or vice versa and nothing occurs.
Sandy

 

[Sandy]

Closed and reopened file as you suggested and it works fine.
Thanks JE - Why did it require rebooting the application though?
Sandy

 

[Dave Peterson]

I didn't, but that was the easiest way to get you to test <vbg>.

You could have just opened the VBE and hit ctrl-g to get to the immediate window
and typed this:

Application.EnableEvents = True
(and hit enter)

Then gone back to test.

But when you reopen excel, .enableevents are true.