Quick Time Entry

[Pete]

I have copied Chip Pearson's code into my Spreadsheet and all seems
well until I enter a time beginning with a zero. Can anyone poinit out
what I'm doing wrong?

If I enter 1500 I get 15:00 which is what I expect

If I enter 0000 for midnight I get an error from the EndMacro, same
thing if I enter 0600

The Cells are formatted to hh:mm

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("B21:C28")) 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(.Value)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
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

Thanks

Pete

 

[Pete_UK]

You've left several Case statements out of Chip's code, such that you
can now only enter a 4-digit number. His original code allowed you to
enter 1 to 6 digits and converted into the appropriate time. So you
would only need to enter 600 (not 0600), and the leading zero would be
added on for you. As the code uses .Value any leading zeros from your
entry are removed, but then you only have a 3-digit number and you do
not have a corresponding Case for that.

I suggest you re-instate the full code.

Hope this helps.

Pete

 

[Pete]

You've left several Case statements out of Chip's code, such that you
can now only enter a 4-digit number. His original code allowed you to
enter 1 to 6 digits and converted into the appropriate time. So you
would only need to enter 600 (not 0600), and the leading zero would be
added on for you. As the code uses .Value any leading zeros from your
entry are removed, but then you only have a 3-digit number and you do
not have a corresponding Case for that.

I suggest you re-instate the full code.

Hope this helps.

Pete









- Show quoted text -

Thanks for that, I only want to enter four digits, but putting CHip's
code back in has fixed my problem.

Pete

 

[Pete_UK]

Okay, glad to hear it.

Pete