I have a code that allows me quick time entry. The time is entered using a
userform with textboxes. It was working ok but now the quick time code does
not work for some reason. The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Range("D4:H5000")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Formula)
Case 1 'e.g., 1=00:01
TimeStr = "0:0" & .Value
Case 2 'e.g., 12=001.2
TimeStr = "00:" & Right(.Value, 2)
Case 3 'e.g., 735=73.5
TimeStr = Left(.Value, 1) & ":" & Right(.Value, 2)
Case 4 'e.g., 1234=123.4
TimeStr = Left(.Value, 2) & ":" & Right(.Value, 2)
Case 5 'e.g., 12345 = 1234.5 NOT 12:03:45
TimeStr = Left(.Value, 3) & ":" & Right(.Value, 2)
Case 6 'e.g., 123456 = 1234.5 NOT 12:03:45
TimeStr = Left(.Value, 4) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
..Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub
--
hol

As a guess your events are not firing... Application.enableevents = false
turns events off and is a persistent setting. While your posted code turns it
back on if you interupt the code wihile debugging or if you have other code
that neglects to turn event back on then your system essentially has no more
events until you run code similar to this...

Sub FixEvents()
application.enableevents = true
end sub

It is a persistent setting so rebooting and such has no effect. You need to
use code to reset your events...
--
HTH...

Jim Thomlinson


"hol" wrote:

I have a code that allows me quick time entry. The time is entered using a
userform with textboxes. It was working ok but now the quick time code does
not work for some reason. The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Range("D4:H5000")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Formula)
Case 1 'e.g., 1=00:01
TimeStr = "0:0" & .Value
Case 2 'e.g., 12=001.2
TimeStr = "00:" & Right(.Value, 2)
Case 3 'e.g., 735=73.5
TimeStr = Left(.Value, 1) & ":" & Right(.Value, 2)
Case 4 'e.g., 1234=123.4
TimeStr = Left(.Value, 2) & ":" & Right(.Value, 2)
Case 5 'e.g., 12345 = 1234.5 NOT 12:03:45
TimeStr = Left(.Value, 3) & ":" & Right(.Value, 2)
Case 6 'e.g., 123456 = 1234.5 NOT 12:03:45
TimeStr = Left(.Value, 4) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub
--
hol

Hi Jim,
I run the code you posted me on the sheet that holds the data, with no
effect. If I manually enter the time, the quick time code works its only when
I use the userform it doesnt work.
Was I ment to put the code you gave me somewhere else?
--
hol


"Jim Thomlinson" wrote:

As a guess your events are not firing... Application.enableevents = false
turns events off and is a persistent setting. While your posted code turns it
back on if you interupt the code wihile debugging or if you have other code
that neglects to turn event back on then your system essentially has no more
events until you run code similar to this...

Sub FixEvents()
application.enableevents = true
end sub

It is a persistent setting so rebooting and such has no effect. You need to
use code to reset your events...
--
HTH...

Jim Thomlinson


"hol" wrote:

I have a code that allows me quick time entry. The time is entered using a
userform with textboxes. It was working ok but now the quick time code does
not work for some reason. The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Range("D4:H5000")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Formula)
Case 1 'e.g., 1=00:01
TimeStr = "0:0" & .Value
Case 2 'e.g., 12=001.2
TimeStr = "00:" & Right(.Value, 2)
Case 3 'e.g., 735=73.5
TimeStr = Left(.Value, 1) & ":" & Right(.Value, 2)
Case 4 'e.g., 1234=123.4
TimeStr = Left(.Value, 2) & ":" & Right(.Value, 2)
Case 5 'e.g., 12345 = 1234.5 NOT 12:03:45
TimeStr = Left(.Value, 3) & ":" & Right(.Value, 2)
Case 6 'e.g., 123456 = 1234.5 NOT 12:03:45
TimeStr = Left(.Value, 4) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub
--
hol