Hi

Does anyone have a method for entering any number, say, 0800 and it will
automatically insert a colon in between the 08 and 00. I have a method of
automatically changing data entered in lower case to upper case, but cannot
work this one out - Can you?

--
Kind Regards

Mick

See


http://cpearson.com/excel/DateTimeEntry.htm


In article ,
"Mick Southam" wrote:

Does anyone have a method for entering any number, say, 0800 and it will
automatically insert a colon in between the 08 and 00. I have a method of
automatically changing data entered in lower case to upper case, but cannot
work this one out - Can you?

Hi,

Put this in your Worksheet Code Module:


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Application.Intersect(Range("B2:B100"), Target) _
Is Nothing Then GoTo e:

With Target
If .Cells.Count 1 Then GoTo e:
If Not IsNumeric(.Value) Then GoTo e:
If .HasFormula Then GoTo e:
If .Value Like "[!0123456789]" Then GoTo e:

Select Case Len(.Value)
Case 1: .Value = "00:0" & .Value
Case 2: .Value = "00:" & .Value
Case 3, 4: .Value = Left(.Value, 1) & ":" & Right(.Value, 2)
Case Else: .ClearContents
End Select

If .Value = 1 Then .ClearContents
.NumberFormatLocal = "HH:MM"

End With

e:
Application.EnableEvents = True

End Sub


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--