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