Hi Patrick

For time code see
http://www.cpearson.com/excel/DateTimeEntry.htm

Then change the format to text before
.Value = TimeValue(TimeStr)

Or maybe you can add a ' before it

.Value = "'" & TimeValue(TimeStr)

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Patrick Simonds" wrote in message ...
Column F of my worksheet is formatted as Text. The code below is intended to
enter : in the middle of a four digit number (18:45 when 1845 is
entered). This column must be entered as text (not a time format) because it
is exported to a reports writing software which will not recognize the
number if it is not text. I have two problems:

1. While the code below works great if you enter the number as 1845, if you
enter 18:45, it gets converted to 00:01 (although the cell is still
formatted as text).

2. My bigger problem is that after I enter the number in column F and then
click (or Tab) to the next column the code works as intended but it then
changes the format of the cell in the adjoining column from a number format
to a text format, and I have no idea why.

If you can help in only one area, number 2 is my most critical problem.
1845 is just a representative number. the input to the column is based on
the 24 hour (military) time format.



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim e

ActiveSheet.Unprotect

On Error GoTo ErrorHandler

If Not Application.Intersect(Target, Range("F3:F8000")) Is Nothing Then

If ActiveCell < "" Then GoTo ErrorHandler

With ActiveCell
.NumberFormat = "@"
e = Left(Format(Target.Value, "0000"), 4)
Application.EnableEvents = False
Target.Formula = Left(e, 2) & ":" & Right(e, 2)
End With
End If

ErrorHandler:

Exit Sub:

End Sub