oceanus
06-16-2012, 11:26 AM
As I was searching Google for help with entering time without using a colon, I found Steiner's VBA code to Enter Times Without the Colon to be pretty helpful. Now I'm wanting to be able to use a similar code to format, for example, "345", as minutes and seconds - mm:ss or 0:03:45 AM. Steiner's code now formats my entry of "345" as hours and minutes or 3:45:00 AM. I think I could divide the results of the code by 60 and that should change it to min and sec but I'm at a loss on how to do that.
Here is Steiner's code I copied from his post Enter Times Without the Colon.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub
'Define the range where you want the code to work (our example is "C:G").
'Change within the " marks
If Intersect(Target, Range("C:G")) Is Nothing Then Exit Sub
On Error Goto errHandler:
With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False
Select Case .Value
Case 0
.NumberFormat = "hh:mm"
Case 1 To 99
.Value = TimeSerial(0, .Value, 0)
.NumberFormat = "hh:mm"
Case 100 To 2399
.Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0)
.NumberFormat = "hh:mm"
Case 10000 To 235959
.Value = TimeSerial(Int(.Value / 10000), _
Int((.Value Mod 10000) / 100), .Value Mod 100)
.NumberFormat = "hh:mm:ss"
Case 240000 To 245959
.Value = TimeSerial(0, Int((.Value Mod 10000) / 100), .Value Mod 100)
.NumberFormat = "hh:mm:ss"
Case Else
End Select
End If
End With
errHandler:
Application.EnableEvents = True
End Sub
Thanks for your help.
Here is Steiner's code I copied from his post Enter Times Without the Colon.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub
'Define the range where you want the code to work (our example is "C:G").
'Change within the " marks
If Intersect(Target, Range("C:G")) Is Nothing Then Exit Sub
On Error Goto errHandler:
With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False
Select Case .Value
Case 0
.NumberFormat = "hh:mm"
Case 1 To 99
.Value = TimeSerial(0, .Value, 0)
.NumberFormat = "hh:mm"
Case 100 To 2399
.Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0)
.NumberFormat = "hh:mm"
Case 10000 To 235959
.Value = TimeSerial(Int(.Value / 10000), _
Int((.Value Mod 10000) / 100), .Value Mod 100)
.NumberFormat = "hh:mm:ss"
Case 240000 To 245959
.Value = TimeSerial(0, Int((.Value Mod 10000) / 100), .Value Mod 100)
.NumberFormat = "hh:mm:ss"
Case Else
End Select
End If
End With
errHandler:
Application.EnableEvents = True
End Sub
Thanks for your help.