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