dleckie
11-07-2008, 11:01 AM
Hi all,
I was helped back in Sept with some code that entered the colon ( : ) in automatically when a number was entered for time. Example 1234 = 12:34:00.
Now my company (with their infinite wisdom) had decide to upgrade MS Office from 2003 to 2007. Unfortunatelly the code will not work in Office 2007.
Can anyone help me with this issure. The code is supplied below.
Thanks for you help.
Dave
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String
On Error GoTo EndMacro
'Enter the range in the spreadsheet where you will be entering time
If Application.Intersect(Target, Range("A1:AR117")) 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)
.NumberFormat = "h:mm:ss" 'Comment this line out if you want to have AM/PM show.
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub
I was helped back in Sept with some code that entered the colon ( : ) in automatically when a number was entered for time. Example 1234 = 12:34:00.
Now my company (with their infinite wisdom) had decide to upgrade MS Office from 2003 to 2007. Unfortunatelly the code will not work in Office 2007.
Can anyone help me with this issure. The code is supplied below.
Thanks for you help.
Dave
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String
On Error GoTo EndMacro
'Enter the range in the spreadsheet where you will be entering time
If Application.Intersect(Target, Range("A1:AR117")) 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)
.NumberFormat = "h:mm:ss" 'Comment this line out if you want to have AM/PM show.
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub