|
Time entry and calculation
I have the following code (courtesy of this discussion group) in a w/b that
allows time taken on a task to be input and calculated. My problem now is that it does allow for input of hours worked in excess of 23:59. Is anybody able to suggest how the code can be amended to allow for this. Code is: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"), Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"), Range("s:s"), Range("u:u"), _ Range("w:w"), Range("y:y"))) 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 -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. |
Time entry and calculation
Hi Russell
Case 4 ' e.g., 1234 = 12:34 If .Value 2359 Then GoTo EndMacro TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) -- Regards Roger Govier Russell Dawson wrote: I have the following code (courtesy of this discussion group) in a w/b that allows time taken on a task to be input and calculated. My problem now is that it does allow for input of hours worked in excess of 23:59. Is anybody able to suggest how the code can be amended to allow for this. Code is: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"), Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"), Range("s:s"), Range("u:u"), _ Range("w:w"), Range("y:y"))) 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 |
Time entry and calculation
I meant to say it does NOT allow for input in excess of 23:59.
-- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Russell Dawson" wrote: I have the following code (courtesy of this discussion group) in a w/b that allows time taken on a task to be input and calculated. My problem now is that it does allow for input of hours worked in excess of 23:59. Is anybody able to suggest how the code can be amended to allow for this. Code is: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"), Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"), Range("s:s"), Range("u:u"), _ Range("w:w"), Range("y:y"))) 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 -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. |
Time entry and calculation
Try this:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"), _ Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"), _ Range("s:s"), Range("u:u"), _ Range("w:w"), Range("y:y"))) 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 dayno = 0 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 hourno = Val(Left(.Value, 2)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 hourno = Val(Left(.Value, 1)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 hourno = Val(Left(.Value, 2)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) + dayno .NumberFormat = "[h]:mm:ss" End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub -- Regards! Stefi €˛Russell Dawson€¯ ezt Ć*rta: I have the following code (courtesy of this discussion group) in a w/b that allows time taken on a task to be input and calculated. My problem now is that it does allow for input of hours worked in excess of 23:59. Is anybody able to suggest how the code can be amended to allow for this. Code is: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"), Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"), Range("s:s"), Range("u:u"), _ Range("w:w"), Range("y:y"))) 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 -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. |
Time entry and calculation
Thanks Roger
I substituted the original with your suggestion but it still doesn't work. I have the cells formatted as custom h:mm. When an input in excess of 2359 is made the message 'You did not enter a valid time' is given. In the formula bar it shows '12/02/1907 00:00:00' when 2600 is entered for instance. As you probably appreciate VBA is not my forte and I am grateful for any assistance. Regards -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Roger Govier" wrote: Hi Russell Case 4 ' e.g., 1234 = 12:34 If .Value 2359 Then GoTo EndMacro TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) -- Regards Roger Govier Russell Dawson wrote: I have the following code (courtesy of this discussion group) in a w/b that allows time taken on a task to be input and calculated. My problem now is that it does allow for input of hours worked in excess of 23:59. Is anybody able to suggest how the code can be amended to allow for this. Code is: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"), Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"), Range("s:s"), Range("u:u"), _ Range("w:w"), Range("y:y"))) 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 . |
Time entry and calculation
Hi Stefi
Excellent solution. might I suggest one further modification, as any entry ending in 60 causes a problem whatever the input length. I think the inclusion of this If clause at the point indicated tarps for this. Application.EnableEvents = False If Right(Target.Value, 2) = "60" Then MsgBox "Time entered cannot end in 60" Target.Value = "" Target.Activate Application.EnableEvents = True Exit Sub End If With Target If .HasFormula = False Then -- Regards Roger Govier Stefi wrote: Try this: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"), _ Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"), _ Range("s:s"), Range("u:u"), _ Range("w:w"), Range("y:y"))) 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 dayno = 0 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 hourno = Val(Left(.Value, 2)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 hourno = Val(Left(.Value, 1)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 hourno = Val(Left(.Value, 2)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) + dayno .NumberFormat = "[h]:mm:ss" End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub |
Time entry and calculation
You got suggestions for your real question, but just to save some typing, you
could use: If Application.Intersect(Target, _ me.Range("E:E,g:g,k:k,m:m,o:o,q:q,s:s,u:u,w:w,y:y" )) Is Nothing Then Exit Sub End If I like to qualify the range and the Me keyword says that this range belongs to the object (the sheet in this case) owning the code. Russell Dawson wrote: I have the following code (courtesy of this discussion group) in a w/b that allows time taken on a task to be input and calculated. My problem now is that it does allow for input of hours worked in excess of 23:59. Is anybody able to suggest how the code can be amended to allow for this. Code is: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"), Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"), Range("s:s"), Range("u:u"), _ Range("w:w"), Range("y:y"))) 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 -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. -- Dave Peterson |
Time entry and calculation
Hi Roger,
Thanks for the appreciation. As far as 60 ending is concerned, the original code also missed to handle this issue even with hour numbers less then 24, and the OP didn't include it, I focused on the written request. Nevertheless I consolidated suggestions from you and Dave, this is the final form, though I guess that the Select Case code could be written in a more compact way. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, _ Me.Range("E:E,g:g,k:k,m:m,o:o,q:q,s:s,u:u,w:w,y:y" )) 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 If Right(Target.Value, 2) = "60" Then MsgBox "Time entered cannot end in 60 (only 59)!" Target.Value = "" Target.Activate Application.EnableEvents = True Exit Sub End If With Target If .HasFormula = False Then dayno = 0 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 hourno = Val(Left(.Value, 2)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 hourno = Val(Left(.Value, 1)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 hourno = Val(Left(.Value, 2)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) + dayno .NumberFormat = "[h]:mm:ss" End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub -- Regards! Stefi €˛Roger Govier€¯ ezt Ć*rta: Hi Stefi Excellent solution. might I suggest one further modification, as any entry ending in 60 causes a problem whatever the input length. I think the inclusion of this If clause at the point indicated tarps for this. Application.EnableEvents = False If Right(Target.Value, 2) = "60" Then MsgBox "Time entered cannot end in 60" Target.Value = "" Target.Activate Application.EnableEvents = True Exit Sub End If With Target If .HasFormula = False Then -- Regards Roger Govier Stefi wrote: Try this: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"), _ Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"), _ Range("s:s"), Range("u:u"), _ Range("w:w"), Range("y:y"))) 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 dayno = 0 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 hourno = Val(Left(.Value, 2)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 hourno = Val(Left(.Value, 1)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 hourno = Val(Left(.Value, 2)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) + dayno .NumberFormat = "[h]:mm:ss" End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub . |
Time entry and calculation
Roger, Stefi and Dave
Many thanks for your input. It works, but I don't really need to tell you that do I? It's humbling to have some of those whom I consider to be some of the 'big guns' helping me out. Kind regards. -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Stefi" wrote: Hi Roger, Thanks for the appreciation. As far as 60 ending is concerned, the original code also missed to handle this issue even with hour numbers less then 24, and the OP didn't include it, I focused on the written request. Nevertheless I consolidated suggestions from you and Dave, this is the final form, though I guess that the Select Case code could be written in a more compact way. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, _ Me.Range("E:E,g:g,k:k,m:m,o:o,q:q,s:s,u:u,w:w,y:y" )) 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 If Right(Target.Value, 2) = "60" Then MsgBox "Time entered cannot end in 60 (only 59)!" Target.Value = "" Target.Activate Application.EnableEvents = True Exit Sub End If With Target If .HasFormula = False Then dayno = 0 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 hourno = Val(Left(.Value, 2)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 hourno = Val(Left(.Value, 1)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 hourno = Val(Left(.Value, 2)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) + dayno .NumberFormat = "[h]:mm:ss" End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub -- Regards! Stefi €˛Roger Govier€¯ ezt Ć*rta: Hi Stefi Excellent solution. might I suggest one further modification, as any entry ending in 60 causes a problem whatever the input length. I think the inclusion of this If clause at the point indicated tarps for this. Application.EnableEvents = False If Right(Target.Value, 2) = "60" Then MsgBox "Time entered cannot end in 60" Target.Value = "" Target.Activate Application.EnableEvents = True Exit Sub End If With Target If .HasFormula = False Then -- Regards Roger Govier Stefi wrote: Try this: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"), _ Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"), _ Range("s:s"), Range("u:u"), _ Range("w:w"), Range("y:y"))) 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 dayno = 0 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 hourno = Val(Left(.Value, 2)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 hourno = Val(Left(.Value, 1)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 hourno = Val(Left(.Value, 2)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) + dayno .NumberFormat = "[h]:mm:ss" End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub . |
Time entry and calculation
You are welcome! Thanks for the feedback!
Clicking the YES button will be appreciated. But I don't understand your reply! Why is it humbling for a student or for a beginner to learn from more experienced people? We also were beginners and learned from our teachers! -- Regards! Stefi €˛Russell Dawson€¯ ezt Ć*rta: Roger, Stefi and Dave Many thanks for your input. It works, but I don't really need to tell you that do I? It's humbling to have some of those whom I consider to be some of the 'big guns' helping me out. Kind regards. -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Stefi" wrote: Hi Roger, Thanks for the appreciation. As far as 60 ending is concerned, the original code also missed to handle this issue even with hour numbers less then 24, and the OP didn't include it, I focused on the written request. Nevertheless I consolidated suggestions from you and Dave, this is the final form, though I guess that the Select Case code could be written in a more compact way. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, _ Me.Range("E:E,g:g,k:k,m:m,o:o,q:q,s:s,u:u,w:w,y:y" )) 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 If Right(Target.Value, 2) = "60" Then MsgBox "Time entered cannot end in 60 (only 59)!" Target.Value = "" Target.Activate Application.EnableEvents = True Exit Sub End If With Target If .HasFormula = False Then dayno = 0 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 hourno = Val(Left(.Value, 2)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 hourno = Val(Left(.Value, 1)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 hourno = Val(Left(.Value, 2)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) + dayno .NumberFormat = "[h]:mm:ss" End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub -- Regards! Stefi €˛Roger Govier€¯ ezt Ć*rta: Hi Stefi Excellent solution. might I suggest one further modification, as any entry ending in 60 causes a problem whatever the input length. I think the inclusion of this If clause at the point indicated tarps for this. Application.EnableEvents = False If Right(Target.Value, 2) = "60" Then MsgBox "Time entered cannot end in 60" Target.Value = "" Target.Activate Application.EnableEvents = True Exit Sub End If With Target If .HasFormula = False Then -- Regards Roger Govier Stefi wrote: Try this: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Union(Range("E:E"), Range("g:g"), _ Range("i:i"), Range("k:k"), Range("m:m"), Range("o:o"), Range("q:q"), _ Range("s:s"), Range("u:u"), _ Range("w:w"), Range("y:y"))) 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 dayno = 0 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 hourno = Val(Left(.Value, 2)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 hourno = Val(Left(.Value, 1)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 hourno = Val(Left(.Value, 2)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) + dayno .NumberFormat = "[h]:mm:ss" End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub . |
Time entry and calculation
Hi Stefi
I hadn't meant my suggestion about the 60 ending to sound critical. I realise that the original code didn't cater for it, I was just suggesting a further addition to the excellent work you had already done. -- Regards Roger Govier Stefi wrote: Hi Roger, Thanks for the appreciation. As far as 60 ending is concerned, the original code also missed to handle this issue even with hour numbers less then 24, and the OP didn't include it, I focused on the written request. Nevertheless I consolidated suggestions from you and Dave, this is the final form, though I guess that the Select Case code could be written in a more compact way. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, _ Me.Range("E:E,g:g,k:k,m:m,o:o,q:q,s:s,u:u,w:w,y:y" )) 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 If Right(Target.Value, 2) = "60" Then MsgBox "Time entered cannot end in 60 (only 59)!" Target.Value = "" Target.Activate Application.EnableEvents = True Exit Sub End If With Target If .HasFormula = False Then dayno = 0 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 hourno = Val(Left(.Value, 2)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 hourno = Val(Left(.Value, 1)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 hourno = Val(Left(.Value, 2)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) + dayno .NumberFormat = "[h]:mm:ss" End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub |
Time entry and calculation
Anyway, your suggestion was very useful, I never had thought of that by myself!
-- Regards! Stefi €˛Roger Govier€¯ ezt Ć*rta: Hi Stefi I hadn't meant my suggestion about the 60 ending to sound critical. I realise that the original code didn't cater for it, I was just suggesting a further addition to the excellent work you had already done. -- Regards Roger Govier Stefi wrote: Hi Roger, Thanks for the appreciation. As far as 60 ending is concerned, the original code also missed to handle this issue even with hour numbers less then 24, and the OP didn't include it, I focused on the written request. Nevertheless I consolidated suggestions from you and Dave, this is the final form, though I guess that the Select Case code could be written in a more compact way. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, _ Me.Range("E:E,g:g,k:k,m:m,o:o,q:q,s:s,u:u,w:w,y:y" )) 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 If Right(Target.Value, 2) = "60" Then MsgBox "Time entered cannot end in 60 (only 59)!" Target.Value = "" Target.Activate Application.EnableEvents = True Exit Sub End If With Target If .HasFormula = False Then dayno = 0 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 hourno = Val(Left(.Value, 2)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 hourno = Val(Left(.Value, 1)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 hourno = Val(Left(.Value, 2)) dayno = hourno \ 24 remhour = hourno Mod 24 TimeStr = Str(remhour) & ":" & _ Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) + dayno .NumberFormat = "[h]:mm:ss" End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub . |
| All times are GMT +1. The time now is 10:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com