VB routine to change display for time not working

  • Thread starter Thread starter Nevets
  • Start date Start date
N

Nevets

I got some help from someone a while ago in order to make it easier to enter
information in a spreadsheet. I wanted to make it so that if someone entered
the digits corresponding to a time without the colon between the hh and mm
values, the program would change the entry to the proper hh:mm format (i.e.
someone enters "530", and when they tab out, the entry changes to "05:30".
I've recently discovered a problem. Here is the VB routine that was
suggested:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tlen As Long, result As String
If Target.Column < 3 Or Target.Column > 4 Then Exit Sub

If Target.Count > 1 Then Exit Sub
tlen = Len(Target.Value)
If tlen = 0 Then Exit Sub
Application.EnableEvents = False
If tlen = 1 And Target.Value = 0 Then GoTo endtime

If Left(Target.Value, 1) = "-" Then
MsgBox ("Cannot have negative values")
Target.Value = ""
GoTo endtime
End If

If tlen < 3 Then
MsgBox ("Invalid Entry")
Target.Value = ""
GoTo endtime
End If

If Format(Target.Value, "hh:mm") <= "23:59" And _
Format(Target.Value, "hh:mm") > "00:00" Then GoTo endtime

If Target.Value > 2359 Then
MsgBox ("Can't enter a time past 23:59")
GoTo endtime
End If

If tlen = 3 Then
result = Left(Target.Value, 1) & ":" & Right(Target.Value, 2)
Else
result = Left(Target.Value, 2) & ":" & Right(Target.Value, 2)
End If
Target.Value = result
endtime:
Target.Value = Format(Target.Value, "hh:mm")

If Format(Target.Value, "hh:mm") = "00:00" Then
Select Case MsgBox("Did you really mean to enter " _
& vbCrLf & "a time of Midnight 00:00?" _
, vbYesNo Or vbQuestion Or vbDefaultButton1, "Query Time input")

Case vbYes

Case vbNo
Target.Value = ""
End Select

End If
Application.EnableEvents = True
End Sub

The problem is that if I enter "0030", I get the message-box: "Invalid
Entry".

Help??
 
Hi

Excel cut off leading zeros when numbers are entered as numbers. Try to
enter 0030 as text by putting a single quote sign first: '0030. Otherwise
the macro has to be changed to accept times entered as two digits.

Hopes this helps.
 
Per suggested prefacing 0030 with an apostrophe.

If you don't want to bother with the apostrophe use this from Chip Pearson.

Enter 0030 or just 30

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Range("E2:E700")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP
 
I don't want to use Per's suggestion, since the whole point is to develop a
template for multiple users, and telling everyone to preface with an
apostrophe won't work.
I'm not a VB whiz by any stretch, so I'm not sure what to do with your code.
Do I just erase all the code that's in there now, and replace it with your
code? If that's so, I don't see anywhere in your code that specifies which
columns I want this code to apply to. The sheet I'm looking to use this for
has all sorts of different formats in various places, and I only want the
rule to run for certain columns.
I tried the following, but it didn't work:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

If Target.Column < 3 Or Target.Column > 4 Then Exit Sub

On Error GoTo EndMacro
If Application.Intersect(Target, Range("E2:E700")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub
 
Nevets,

You need to remove these lines to have the code work on just columns C and D:


If Application.Intersect(Target, Range("E2:E700")) Is Nothing Then
Exit Sub
End If

Note that you need to enter values as military time: 1315 for 1:15 PM.... If you want a restricted
set of times, say between 8AM and 5PM, then you could use this, to enter 445 and get 4:45 PM


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro

If Target.Column < 3 Or Target.Column > 4 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) + _
IIf(Val(Left(.Value, Len(.Value) - 2)) < 7, 12, 0) _
& ":" & Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub



HTH,
Bernie
MS Excel MVP
 
Yes. Delete your existing code and replace with the code I posted.

As written it runs on the range E7:E700.

You added this line to the code I posted.

The code won't run if columns C or D are not selected.

If that's what you want, delete these lines below.

If you want some other columns or ranges post what you want.


Gord
 
Back
Top