Convert values to time: Error Trapping

S

Stringer

I am trying to write a macro that the user can enter time as integers
ie:
100=1:00
30=:30
123=1:23
I have this code, and am wishing to add to the error trapping so tha
the target does not = :99 if the user enters 99
or :72 if 72 is entered
Any ideas?

Code
-------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As Integer
Dim i As String, ni As String
t = Target.Column
If t < 3 Then
i = Target.Value
On Error GoTo error
If i > 1 And i <= 2400 Then
ni = Left(i, Len(i) - 2) & ":" & Right(i, 2)
Application.EnableEvents = False
Target = ni
Application.EnableEvents = True
Else: MsgBox "Must have more than 1 number and not be greater than 2400"
Target.Select
Exit Sub
error: MsgBox "Must be a number"
Target.Select
End If
End If

End Sub
 
J

JBeaucaire

You're parsing the numbers, but not INTO a time format. In a worksheet
function, I would do the same thing you're doing, but like this:

=TIME(0,LEFT(A2,1),RIGHT(A2,2))

So, do the same thing in your macro, make sure the minutes and seconds are
slotting into MINUTE portion of a time function, and the same for SECONDS. By
doing this, you get the following results:

123 = 0:01:23
199 = 0:02:39
 
R

Rick Rothstein

How about something like this instead...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewTime As Variant
If Target.Column < 3 Then
NewTime = Format(Target.Value, "00:00")
If IsDate(NewTime) Then
Application.EnableEvents = False
Target.NumberFormat = "hh:mm"
Target.Value = CDate(NewTime)
Application.EnableEvents = True
Else
Target.NumberFormat = "General"
Target.Select
MsgBox "I am not able to convert that entry into a time value!"
End If
End If
End Sub
 
Top