Solved: Error Message

[maxflia10]

I have the following code to automatically add colons for time and would like a pop-up to appear when the user enters wrong data, i.e. with a colon, more than four numbers.




Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("TimeCells")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
With Target
If Len(Target) = 3 Then .Value = Left(.Value, 1) & ":" & Right(.Value, 2)
If Len(Target) = 4 Then .Value = Left(.Value, 2) & ":" & Right(.Value, 2)
Application.EnableEvents = True
End With
End Sub



TIA

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("TimeCells")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
With Target
If Len(Target) > 4 Or InStr(Target.Value, ":") > 0 Then
MsgBox "Error"
ElseIf Len(Target) = 3 Then
.Value = Left(.Value, 1) & ":" & Right(.Value, 2)
ElseIf Len(Target) = 4 Then
.Value = Left(.Value, 2) & ":" & Right(.Value, 2)
End If
End With
Application.EnableEvents = True
End Sub

[maxflia10]

Thanks xld!

Not homework.....:thumb

[Bob Phillips]

Better



Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit
Application.EnableEvents = False
If Intersect(Target, Range("TimeCells")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
With Target
If InStr(.Text, ":") > 0 Then
MsgBox "Don't enter colons (:)"
ElseIf Not IsNumeric(.Value) Then
MsgBox "Only numbers allowed"
ElseIf Len(.Value) > 4 Then
MsgBox "Too many digits"
ElseIf Len(.Value) = 1 Then
.Value = "0:0" & .Value
ElseIf Len(.Value) = 2 Then
.Value = "0:" & .Value
ElseIf Len(Target) = 3 Then
.Value = Left(.Value, 1) & ":" & Right(.Value, 2)
ElseIf Len(Target) = 4 Then
.Value = Left(.Value, 2) & ":" & Right(.Value, 2)
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

[maxflia10]

xld,

Thanks, glad I came back! However when I tried entering any time from it doesn't enter the colon. Actually the original code couldn't cope with numbers from 0:01 to 0:59

TIA

[Bob Phillips]

It doesn't seem to handle previous entries properly.

See if this works better



Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit
Application.EnableEvents = False
If Intersect(Target, Range("TimeCells")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
With Target
If Not IsNumeric(.Value) Then
MsgBox "Only numbers allowed"
ElseIf Len(.Value) > 4 Then
MsgBox "Too many digits or colons (:) added"
ElseIf Len(.Value) = 1 Then
.Value = "0:0" & .Value
ElseIf Len(.Value) = 2 Then
.Value = "0:" & .Value
ElseIf Len(Target) = 3 Then
.Value = Left(.Value, 1) & ":" & Right(.Value, 2)
ElseIf Len(Target) = 4 Then
.Value = Left(.Value, 2) & ":" & Right(.Value, 2)
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

[maxflia10]

xld,

Still not working. In cells where there were entries, it returns 0:00 and for new entries, it returns the number entered without the colon.

[Bob Phillips]

No it doesns't. It works fine as far as i can see.

[maxflia10]

xld,

When I apply the code to the worksheet that had the previous code, it doesn't work. However when I tried it on a new worksheet, it worked!

Mahalo Nui Loa for your time and expertise. It's much appreciated!!!