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

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

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

I do have the code only on the worksheet and not the workbook and I am
only using it on the specific worksheet.
The code does work for me also and fits my needs perfectly. The
problem arises when I reopen Excel. (I do make sure it is saved
first) I can close the workbook and reopen it and it will work fine
as long as I do not close and reopen Excel in the mean time. When you
did it, were you able to close and reopen Excel and still have it
work?




On Dec 26, 1:46 pm, "Chip Pearson" wrote:
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. Seewww.cpearson.com/Excel/AppEvent.aspxfor 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 Consultingwww.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

I finally have a solution to my problem. I needed to indicate that
the location of the workbook was in a Trusted Location in the Macro
Security section of the Developer tab.

Dennis


On Dec 26 2007, 3:52*pm, den4673 wrote:
I do have the code only on the worksheet and not the workbook and I am
only using it on the specific worksheet.
The code does work for me also and fits my needs perfectly. *The
problem arises when I reopen Excel. *(I do make sure it is saved
first) *I can close the workbook and reopen it and it will work fine
as long as I do not close and reopen Excel in the mean time. *When you
did it, were you able to close and reopen Excel and still have it
work?

On Dec 26, 1:46 pm, "Chip Pearson" wrote:



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. Seewww.cpearson.com/Excel/AppEvent.aspxforinformation 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 Consultingwww.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- Hide quoted text -

- Show quoted text -