Validation problems

[Les Isaacs]

Hello All

I have a worksheet that will be used for simple data entry of name, address,
etc etc. - 1 column per field. One of the columns will be 'start time'
(column S) and the next column will be 'end time' (column T). I have used
Chip Pearson's code to help with the formatting of the times, and this works
great (so 8.25am is entered as 825 etc - the code is below), but the problem
now is that I need to ensure that 'end time' is > 'start time'. When I set
the validation of the cells in the 'end time' column by selecting the entire
column T, then doing Data>Validation, selecting Custom and entering the
formula =T1>S1 this doesn't work - it still allows me to enter times in
column T that are earlier than the corresponding row's column S time. It has
been suggested that the validation may need to be included in the code - but
this is beyond my powers!

Hope someone can help.
Many thanks
Les

The formatting code:

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("S2:T99")) 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

 

[Bob Phillips]

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("S2:T99")) 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
If (.Column = 19 And .Offset(0, 1).Value <> "" And _
.Offset(0, 1).Value < .Value) Or _
(.Column = 20 And .Offset(0, -1).Value <> "" And _
.Offset(0, -1).Value > .Value) Then
MsgBox "End time cannot be earlier than start time"
Me.Cells(.Row, "T").Value = ""
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Les Isaacs]

Bob

Many thanks for your reply (again!)

Unfortunately though I can't get your code to work. The formatting works
fine, but the validation seems to do nothing - I can enter any time I like
in column T and it never rejects a value that is an earlier time than that
in clomun S (same row).

In trying to understand this, please could you tell me what the "If (.Column
= 19" part of your code means? Am I applying the validation to the wrong
area?

The other thing I should have pointed out is that in fact I have 14 pairs of
start time/end time columns (Monday morning start, Monday morning end,
Monday afternoon start, Monday morning end, Tuesday morning start ... etc
etc). The validation must be applied to each 'end time' column (i.e. columns
T, V, X, Z, AA etc etc.). Do I achieve this be repeating the "If (.Column =
19" part of your code for each 'end time' column?

Thanks again for your continued help.
Les

 

[Les Isaacs]

Bob

Now I'm really confused!

I had been experimenting with the code, tried various things, got nowhere -
except that now the formatting has stopped working!
I therefore deleted the code and re copied/pasted it from your post, but
whereas last time this worked OK now it doesn't.
Whatever I enter in any of the cels, the displayed value is always 12:00 AM
(although I can see from the formula bar that the actual value is e.g.
14/07/2085 00:00:00). I cannot for the life of me think what I have done
differently using your code this time compared to last time (just 20 minutes
ago!).

Hope you don't give up on me.

Thanks
Les

 

[Bob Phillips]

Les,

Me too, as it works fine for me, formatting and validation.

The 19 is referring to column S, 20 is T. If you want extra columns, that
could get difficult. I don't know how you envisage handling the extra
columns, but this amendment might help in catering for it (assuming it works
for you)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const WS_RANGE As String = "S2:T99"
Dim TimeStr As String

On Error GoTo EndMacro
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Application.Intersect(Target, Me.Range(WS_RANGE)) Is Nothing 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
If Me.Cells(.Row, Me.Range(WS_RANGE).Columns(1).Column).Value <> ""
And _
Me.Cells(.Row, Me.Range(WS_RANGE).Columns(2).Column).Value <> ""
And _
Me.Cells(.Row, Me.Range(WS_RANGE).Columns(1).Column).Value > _
Me.Cells(.Row, Me.Range(WS_RANGE).Columns(2).Column).Value Then
MsgBox "Start time earlier than end time"
.Value = ""
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Les Isaacs]

Bob

You are a star!

I still don't know what I had done differently last time, but with your new
code the formatting validation worked fine and also the validation on
columns S/T. I then simply duplicated the "If Me.Cells(.Row,
Me.Range(WS_RANGE).Columns(1)...." statement 14 times, incrementing the
Columns(1) and Columns(1) values to 2 and 4, then 5 and 6, etc etc, and all
is wonderfull!!

I am very grateful for your help.

I now have another (simpler) validation issue, which I should perhaps post
in a new thread but though I would add it here in case it really is very
simple and you don't mind my doing so. One of the columns is for the
National Insurance number, and this must be in the format AA000000A (i.e.
two letters followed by 6 numerics followed by one letter) - BUT only
certain combinations of the first two letters are valid (e.g. WD is OK but
WR is not), and only certain letters at the end are valid: I have a list of
the valid combinations of first two letters (there are 48 of them!) and the
valid last letters (there are 7).

I have tried various things in the Data>Validation>Custom window, but can't
get it right. Is it simple?

Thanks again for all your help.
Les

 

[Bob Phillips]

If you have list of valids, you can use

=AND(ISNUMBER(MATCH(LEFT(C2,2),valid_first,0)),ISNUMBER(MATCH(RIGHT(C2,1),valid_last,0)),ISNUMBER(--MID(C2,3,5)))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)