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 that
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

--------------------


Thanks


--
Stringer
------------------------------------------------------------------------
Stringer's Profile: http://www.thecodecage.com/forumz/member.php?userid=117
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64692

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

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Stringer" wrote:


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 that
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

--------------------


Thanks


--
Stringer
------------------------------------------------------------------------
Stringer's Profile: http://www.thecodecage.com/forumz/member.php?userid=117
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64692

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

--
Rick (MVP - Excel)


"Stringer" wrote in message
...

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 that
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

--------------------


Thanks


--
Stringer
------------------------------------------------------------------------
Stringer's Profile:
http://www.thecodecage.com/forumz/member.php?userid=117
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64692

Thanks Rick, that works very well.


--
Stringer
------------------------------------------------------------------------
Stringer's Profile: http://www.thecodecage.com/forumz/member.php?userid=117
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64692