I'm having trouble entering time without using ":" as a delimiter.
I just watched the Mr Excel video on Event Handlers:
http://www.youtube.com/user/bjele123#p/c/42/FVTB6ruazJU<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
... and was psyched to learn and use the event handler for time entry. I used this code:
If Target.Column = 3 Then
Application.EnableEvents = False
Target.Value = Left(Target.Value, 2) & ":" & Right(Target.Value, 2)
Application.EnableEvents = True
End If
It works great at turning "1234" into 12:34. Unfortunately, it turns "0123" into 12:23 (in other words, it disregards the zero when evaluating the two leftmost positions)
So I tried this alternative approach:
If Target.Column = 3 Then
Application.EnableEvents = False
Target.Value = Replace(Target.Value, ".", ":")
Application.EnableEvents = True
End If
... and used "." to delimit. Unfortunately, now the problem is at the other end of the string: "7.50" is translated to "7:05". Apparently, the zero on the right is now disregarded.
In both cases, Column C is formatted as "Time 13:30" (in the Format Cells window)
The upside is, I'm learning lots about VBA. The downside is, this timesaver is eating my day! Help, please!
I just watched the Mr Excel video on Event Handlers:
http://www.youtube.com/user/bjele123#p/c/42/FVTB6ruazJU<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
... and was psyched to learn and use the event handler for time entry. I used this code:
If Target.Column = 3 Then
Application.EnableEvents = False
Target.Value = Left(Target.Value, 2) & ":" & Right(Target.Value, 2)
Application.EnableEvents = True
End If
It works great at turning "1234" into 12:34. Unfortunately, it turns "0123" into 12:23 (in other words, it disregards the zero when evaluating the two leftmost positions)
So I tried this alternative approach:
If Target.Column = 3 Then
Application.EnableEvents = False
Target.Value = Replace(Target.Value, ".", ":")
Application.EnableEvents = True
End If
... and used "." to delimit. Unfortunately, now the problem is at the other end of the string: "7.50" is translated to "7:05". Apparently, the zero on the right is now disregarded.
In both cases, Column C is formatted as "Time 13:30" (in the Format Cells window)
The upside is, I'm learning lots about VBA. The downside is, this timesaver is eating my day! Help, please!