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!
 

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:(
 

Forum statistics

Threads
1,081,563
Messages
5,359,617
Members
400,540
Latest member
JimUSMC

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top