Worksheet_Change not changing
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
|