PDA

View Full Version : Solved: To few digits



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

Bob Phillips
12-26-2007, 12:23 PM
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

perhol
12-26-2007, 03:43 PM
well, at first i thought it was perfect.
I changed your
Target.NumberFormat = "hh:mm" to
Target.NumberFormat = "[t]:mm" 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! :clap:
Then, by chance, i entered a lot of letters like (n<shmehghfv,lc,m) and the cell contained these letters! :eek:
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 : pray2:

Bob Phillips
12-26-2007, 04:38 PM
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

perhol
12-26-2007, 05:05 PM
Again i tried to enter like above, and it functions perfect :clap:

Thanks xld :bow: