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!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
 
Upvote 0
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:(
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top