Re: Yet more validation ... challenges!

Bob

Thanks for still being there!

So to clear invalid times it's:
Target.Value = ""
Works perfectly!

Regarding the postcodes, these are entered in column I. Column AX has the
value =ValidatePaostCode(I2) etc., and the validation on column I is =AX2.
When a user initially enters a postcode it is validated correctly whether
they press enter or click in another cell.
The problem only occurs under two situations:

1. a valid postcode is wrongly rejected if the user, immediately after
entering the postcode, clicks on another cell instead of hitting the enter
or an arrow key;

2. when the user enters a valid postcode, then presses the enter or an arrow
key, then re-edits the previously entered postcode to make it an invalid
one, then immediately clicks on another cell (instead of hitting the enter
key).

Hope that help you see where the problem is - an more importantly what I
need to do to fix it! I'm sure it is to do with controlling exactly when the
validation is carried out.

Thanks again.
Les


"Bob Phillips" <bob.NGs@xxxxxxxxxxxxx> wrote in message
news:%23$y$IzBGIHA.1204@xxxxxxxxxxxxxxxxxxxxxxx



"Les Isaacs" <leslie@xxxxxxxxxxxxxxxx> wrote in message
news:OrChE7$FIHA.484@xxxxxxxxxxxxxxxxxxxxxxx
Hello All

Bob/Rick - are you there?!

Having done some more testing with the validations that you have each
helped me with, I have found a couple of problems.

The code that I have for validating times works fine except that, having
entered an invalid time, the invalid time is retained after the error
message is displayed: it must be deleted. The code that validates the
times is below, and I am guessing that I need a line towards the end
(after the message "You did not enter a valid time. Do not use colons
etc. - enter 8.00am as 800, enter 4.30pm as 1630 etc.") that clears the
value. I tried .Value = "" but that didn't work. What can I put?


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

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

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
If Me.Cells(.Row, Me.Range(WS_RANGE).Columns(3).Column).Value <> ""
_
And Me.Cells(.Row, Me.Range(WS_RANGE).Columns(4).Column).Value
<> "" _
And Me.Cells(.Row, Me.Range(WS_RANGE).Columns(3).Column).Value
> _
Me.Cells(.Row, Me.Range(WS_RANGE).Columns(4).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. Do not use colons etc. - " &
vbNewLine & _
"enter 8.00am as 800, enter 4.30pm as 1630 etc."
Target.Value = ""
Application.EnableEvents = True
End Sub


The other problem relates to the postcode validation. The validation
itself is perfect, but it can be 'side-stepped' if the user initially
enters a valid postcode, then edits it so that it becomes an invalid
postcode, and then immediately clicks into another cell: in these
circulstances the validation seems not to be applied at all: and believe
it or not this really did happen today for a 'real' user! I assume it
has something to do with controlling exactly when the validation is
applied - but beyond that I'm lost. Again, I would be extremely grateful
if you could help.

How are you using the function Les? Do you have a postcode in one cell,
and a work*** formula using that function in another?

Then you say a user entered a valid postcode (then moved to another
cell?), then entered an invalid postcode and tabbed/moused to another
cell?

BTW is the function you are using?

Function ValidatePostCode(ByVal PostCode As String) As Boolean
Dim Parts() As String
PostCode = UCase$(PostCode)
Parts = Split(PostCode)
ValidatePostCode = (PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
(Parts(1) Like "#[ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]" And _
(Parts(0) Like "[A-PR-UWYZ]#" Or _
Parts(0) Like "[A-PR-UWYZ]#[0-9A-HJKSTUW]" Or _
Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#" Or _
Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#[0-9ABEHMNPRVWXY]")))
End Function





.