Change Event not running

[butts2805]

Please can you give me some advice on why the following Change event will not work? It dose not seem to run.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
It is place in the worksheet module that it needs to work in.<o></o>
It is a procedure to convert an entry to military time format. EG 1800 become 18:00.<o></o>
Even when entering 2370, it will not bring up the error messages.<o></o>
<o></o>
It did previously work but now not. (Excel 2003)

<o>Private Sub Worksheet_Change(ByVal Target As Range)</o>
<o>Dim TimeStr As String
On Error Resume Next
If Application.Intersect(Target, Range("L4:GG6")) Is Nothing Then
Application.EnableEvents = False
Exit Sub
End If
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub

If Left(Target.Value, 2) > 23 Or Right(Target.Value, 2) > 59 Then
MsgBox "Please Enter A Valid Time value 0000 - 2359"

Target = ""
Target.Activate

Application.EnableEvents = True
Exit Sub
End If

Application.EnableEvents = False

With Target
UserInput = Format(Target.Value, "00:00")
If Len(UserInput) > 1 Then
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
Application.EnableEvents = False
Target = UserInput
Application.EnableEvents = True
End If
End With
End Sub</o>

Many thanks

Malcolm

 

[Andrew Poulsom]

Welcome to the Board.

If a cell outside the range L4:GG6 is changed this:

If Application.Intersect(Target, Range("L4:GG6")) Is Nothing Then
    Application.EnableEvents = False
    Exit Sub
End If

disables events so the code won't fire again. Try removing:

Application.EnableEvents = False

then go to the Immediate window, type:

Application.EnableEvents = True

and press Enter.

 

[schielrn]

Welcome to the Board!

I think maybe it might have to do with the line in bold:

Private Sub Worksheet_Change(ByVal Target As Range)
<?xml:namespace prefix = o /><o:p>Dim TimeStr As String
On Error Resume Next
If Application.Intersect(Target, Range("L4:GG6")) Is Nothing Then
Application.EnableEvents = False
Exit Sub
End If
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
 
If Left(Target.Value, 2) > 23 Or Right(Target.Value, 2) > 59 Then
MsgBox "Please Enter A Valid Time value 0000 - 2359"
 
Target = ""
Target.Activate
 
Application.EnableEvents = True
Exit Sub
End If
 
Application.EnableEvents = False
 
With Target
UserInput = Format(Target.Value, "00:00")
If Len(UserInput) > 1 Then
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
Application.EnableEvents = False
Target = UserInput
Application.EnableEvents = True
End If
End With
End Sub

</o>
<o>I don't see why if you want to exit the sub, that you would disable the events prior to doing that?</o>
<o></o>
<o>So maybe your events are disabled and they just need to be re-eneabled.</o>
<o></o>
<o>Put this line in the immediate window and press enter:</o>
<o></o>
<o>

Application.EnableEvents = True

</o><o>or just run this code:</o>
<o></o>
<o>

sub test()</o:p>
<o:p></o:p><o:p>Application.EnableEvents = True
end sub

</o>

 

[butts2805]

Thank you. I knew it must be something simple.

Thanks

Malcolm