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

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


On Jul 22, 8:21*pm, Pete <psowe...@murton-view.freeserve.co.uk> wrote:
> 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

On 22 Jul, 20:44, Pete_UK <pashu...@auditel.net> wrote:
> 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
>
> On Jul 22, 8:21*pm, Pete <psowe...@murton-view.freeserve.co.uk> wrote:
>
>
>
> > 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- Hide quoted text -
>
> - 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

Okay, glad to hear it.

Pete

On Jul 22, 9:17*pm, Pete <psowe...@murton-view.freeserve.co.uk> wrote:
>
> Thanks for that, I only want to enter four digits, but putting CHip's
> code back in has fixed my problem.
>
> Pete