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

On Aug 3, 11:59 am, "Sandy" wrote:
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...

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
"Sandy" wrote in message
...
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

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)?

In article ,
"Sandy" wrote:

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

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

"Zone" wrote in message
...
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
"Sandy" wrote in message
...
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

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

"JE McGimpsey" wrote in message
...
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)?

In article ,
"Sandy" wrote:

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

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.

Sandy wrote:

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

"JE McGimpsey" wrote in message
...
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)?

In article ,
"Sandy" wrote:

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

--

Dave Peterson