perhol
12-26-2007, 12:13 PM
This Sub change a standard digit into timeformat ([t]:mm).
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Slut
If Not Intersect(Target, Range("TidCol")) Is Nothing And Target <> "" Then
If Target >= 1 And Len(Format(Target, "0")) <= 4 Then
iTime = Left(Format(Target, "0000"), 2)
iMinut = Right(Format(Target, "0000"), 2)
If iTime < 24 And iMinut <= 59 Then
Target = iTime / 24 + iMinut / 24 / 60
Else
Target = ""
End If
Else
If Not Target < 1 Then Target = ""
End If
End If
Slut:
End Sub
Trouble is that some might enter only 2 digits, eg. 12, and that would translate into 00:12.
Some also might enter digits with , (comma) or . (dot). It should only be permitted to enter hole numbers, at least 3 and no more than 4.
The line:
If Target >= 1 And Len(Format(Target, "0")) <= 4 Then prevents entering more tyhan 4 digits.
How do i do that? :dunno
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Slut
If Not Intersect(Target, Range("TidCol")) Is Nothing And Target <> "" Then
If Target >= 1 And Len(Format(Target, "0")) <= 4 Then
iTime = Left(Format(Target, "0000"), 2)
iMinut = Right(Format(Target, "0000"), 2)
If iTime < 24 And iMinut <= 59 Then
Target = iTime / 24 + iMinut / 24 / 60
Else
Target = ""
End If
Else
If Not Target < 1 Then Target = ""
End If
End If
Slut:
End Sub
Trouble is that some might enter only 2 digits, eg. 12, and that would translate into 00:12.
Some also might enter digits with , (comma) or . (dot). It should only be permitted to enter hole numbers, at least 3 and no more than 4.
The line:
If Target >= 1 And Len(Format(Target, "0")) <= 4 Then prevents entering more tyhan 4 digits.
How do i do that? :dunno