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

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

Hi Patrick,

The event you call ("worksheet_sheetchange") is activated whenever you
change something on your worksheet, but only after you have moved away
from the cell that has changed.

For example, you enter "1400" in cell F4.
After you click on another cell (e.g. G4), the sheetchange event fires.

To answer question 2, this means that the Activecell is G4, and your
VBA then assigns text formatting (" .numberformat = "@" ") to that
cell. Your code can be simplified to:

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

e = Left(Format(Target.Value, "0000"), 4)

'** why do you disable events after running it once? * - unless you
have a good reason to do 'this, it probably shouldn't be here **
Application.EnableEvents = False

Target.Formula = Left(e, 2) & ":" & Right(e, 2)
End If
....

Also, you should either get rid of the error handling stuff entirely or
put a message box in the error handler section (otherwise, you'll
silently ignore all the errors):

Errorhandler:
msgbox err.number & " was encountered on " & activesheet.name

On my computer, there was no issue with 1845, so I'm not sure why that
happened on yours.

Daniel



Patrick Simonds wrote:
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