Re: Yet more validation ... challenges!

Bob

Thanks for your reply.

On the cells in column AX there is no validation in place (it is set to 'Any
value'), so the 'Ignore Blank' checkbox is not enabled. I tried adding a
notional validation to AX2 (text length <10), and then unchecking the
'Ignore Blank' checkbox, but that didn't have any effect on either of the 2
problems.

Did you mean I should uncheck the 'Ignore Blank' checkbox for the validation
on columnI ? I tried this, but that also had no effect on either of the 2
problems.

I have noticed that, when I generate the 2nd of the 2 errors described below
the displayed value of the cell in column AX is FALSE (whereas for a valid
postcode it is TRUE). I therefore tried editing the validation for the cells
in columnI from =AX9 to =AND(I9=AX9,AX9<>False), but although this seems to
fix the 2nd of the two problems it is no good because it causes all valid
postcodes to be rejected when initially entered!!

Am I getting anywhere?

Hope you can help - and many thanks once again.
Les


"Bob Phillips" <bob.NGs@xxxxxxxxxxxxx> wrote in message
news:efDT%23eOGIHA.1164@xxxxxxxxxxxxxxxxxxxxxxx
Les,

Try this.

In AX2, go into Data Validation, and on the Settings tab, uncheck the
'Ignore Blank' checkbox.

--
---
HTH

Bob

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



"Les Isaacs" <leslie@xxxxxxxxxxxxxxxx> wrote in message
news:e$A04THGIHA.284@xxxxxxxxxxxxxxxxxxxxxxx
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









.