Solved: To few digits

[perhol]

This Sub change a standard digit into timeformat ([t]:mm).

[vba]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[/vba]
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:
[vba] If Target >= 1 And Len(Format(Target, "0")) <= 4 Then[/vba] prevents entering more tyhan 4 digits.

How do i do that?

[Bob Phillips]

[vba]

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.Value Mod 1 = 0 Then
If Len(Target.Value) >= 3 And Len(Target.Value) <= 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
Target.NumberFormat = "hh:mm"
Else
If Not Target < 1 Then Target = ""
End If
End If
End If
Slut:
End Sub
[/vba]

[perhol]

well, at first i thought it was perfect.
I changed your
[vba]Target.NumberFormat = "hh:mm"[/vba] to
[vba]Target.NumberFormat = "[t]:mm"[/vba] to get the desired timeformat.
I then entered 2 digits - and the cell was emptied.
I then entered 3 and 4 digits (930 and 1930) and the cell was changed to 9:30 and 19:30
I then entered 5 digits and the cell was emptied.
I then entered a lot of digits and the cell was emptied.
Perfect!
Then, by chance, i entered a lot of letters like (n<shmehghfv,lc,m) and the cell contained these letters!
Well, OK, thats a silly entering in a cell supposed to contain a timevalue, but people will enter anything, even if you instruct them very precise what to enter.
It should only be permitted to enter hole numbers, at least 3 and no more than 4, and absolutely nothing else. If not 3 or 4 hole numbers, cell should be emptied.
Can you fix that too

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Slut
Application.EnableEvents = False
If Not Intersect(Target, Range("TidCol")) Is Nothing Then
If IsNumeric(Target.Value) Then
If Target.Value Mod 1 = 0 And Target.Value > 0 Then
If Len(Target.Value) >= 3 And Len(Target.Value) <= 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
Target.NumberFormat = "[t]:mm"
Else
Target.Value = ""
End If
Else
Target.Value = ""
End If
Else
Target.Value = ""
End If
End If
Slut:
Application.EnableEvents = True
End Sub
[/vba]

[perhol]

Again i tried to enter like above, and it functions perfect

Thanks xld