VB routine to change display for time not working

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??
 
P

Per Jessen

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

Gord Dibben

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
 
N

Nevets

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
 
B

Bernie Deitrick

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
 
G

Gord Dibben

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with V-Basic code for displaying times in hh:mm format 1
Trying to run excel vba code when cell value changes from empty to entered value. 1
Excel Target.column for Data Validation would like to target some cells within column 1
Excel [VBA] Check from a cell into another sheet and bring back result in MsgBox 0
How to add cell range that is excluded from VB script 2
Script to make text in uppercase, on workbook level, but not for other opened workbooks. 8
Proper Case in VBA 4
Quick Time Entry 3

Top