Excel 2007 Event Handler and Time Entry problem

Beel

New Member
Joined
Feb 10, 2011
Messages
3
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-com:eek:ffice:eek:ffice" /><o:p></o:p>

... 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!
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Beel

New Member
Joined
Feb 10, 2011
Messages
3
Darn. Now it's not working at all.

Now, if I type in 1234 to the cell, it populates it with this value: 5/18/1903 12:00:00 AM

May I freely admit now that I'm entirely over my head?

I'll have a local VBA guru have a look at my spreadsheet. Maybe he can figure out what I'm doing wrong.

I think the root of the problem is that MrExcel's script was created for the pre- Excel 2007 version, and there's something Excel does now (remove leading zeros and trailing decimal zeros) that's messing me up.

If there were a way to turn that functionality off, maybe this script would behave.
 

Beel

New Member
Joined
Feb 10, 2011
Messages
3
Okay, got it working (sorta kinda, anyway). See code below.

It works just great for just about every type of input (123, 0123, 1215, 2200, etc.), except if you type the data in with a colon (1:23).

Code:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Column = 3) Or (Target.Column = 4) Then
Application.EnableEvents = False
If Target.Value < 999 Then
Target.Value = Left(Target.Value, 1) & ":" & Right(Target.Value, 2)
Else
Target.Value = Left(Target.Value, 2) & ":" & Right(Target.Value, 2)
End If

Application.EnableEvents = True

End If
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I tried plugging in a test and error handler bit, below, but that doesn't seem to help:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Column = 3) Or (Target.Column = 4) Then
Application.EnableEvents = False
If Right(Target.Value, 3) = ":" Then GoTo bailage
If Target.Value < 999 Then
Target.Value = Left(Target.Value, 1) & ":" & Right(Target.Value, 2)
Else
Target.Value = Left(Target.Value, 2) & ":" & Right(Target.Value, 2)
' bailage:
End If
Application.EnableEvents = True
End If
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NONE OF THIS would be a problem if I could just tell Excel 2007 to STOP REMOVING zeros from my numeric data (001 or .100)! Bah! :eek:(
 

Watch MrExcel Video

Forum statistics

Threads
1,090,490
Messages
5,414,843
Members
403,548
Latest member
frostinheart

This Week's Hot Topics

Top