Just so we're on the same page....

The code on my web site and the code you are using will change the entry
from numeric to time ONLY on the worksheet that contains the code. It will
not work on another worksheet in the same workbook or in another workbook
file.

If you need it to work on all worksheets in a workbook, move the code from
the worksheet module to the ThisWorkbook module, and change

Private Sub Worksheet_Change(ByVal Target As Range)
to
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If you need it to work on all open workbooks (an approach I would strongly
recommend against), you'll need to use application events. See
www.cpearson.com/Excel/AppEvent.aspx for information about application
events.

The code as you have written works fine for me in Excel 2003 and Excel 2007.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)



"den4673" wrote in message
...
On Dec 25, 5:10 pm, "Chip Pearson" wrote:
I would search for any code (in any workbook or add-in that is loaded when
Excel starts) to see if there is an Application.EnableEvents = False line
of
code.

Short of that, post the code that you are using.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consultingwww.cpearson.com
(email on the web site)

"den4673" wrote in message

...



I copied an example from Chip Pearson's web site for time quick
entry. I modified it to fit my needs (expanded the target range only)
and it worked fine. The problem I am having is that when I reopened
Excel the next day it did not work. I have since found out that I can
open and close the workbook and as long as I do not close Excel it
will work. If I close Excel and reopen it then I have to recopy the
example from Chip Pearson to make it work.

I know I am missing something here but I do not know what. Any help
is appreciated.

Thanks
Dennis- Hide quoted text -

- Show quoted text -

The code is posted below and it works very well until I reopen Excel.
It sounds like the Application.EnableEvents = False line of code is
causing the problem but I have no idea where to look for this. I am
using Excel 2007 and have saved it both as an .xlsm and as .xls for
Excel 97-2003.

Thank you for responding.

Dennis


Private Sub Worksheet_Change(ByVal Target As Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Range("A7:P90")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True

End Sub