Re: Yet more validation ... challenges!

Bob

Sorry, I hope I haven't pushed the boundary of of your good will too far!
Actually, I never cease to be amazed by the fact that such high quality help
is freely available in these newsgroups. I for one have benefitted hugely
from them over the years (mainly from the access ng's) - and also hugely
from yourself and Rick recently.

I'm going to ask - for the first time: why do you all do it? Is it a means
of geeting fee-paying contract work, or is the MVP qualification with
something tangible to you, or are you all simply very generous with your
time and expertise?

Whatever the answer, the result is a rare (unique?) example of where the
world is as it should be!

That was a new and more specific way of saying "Thanks"!

Les
BTW: I like "By the cringe", but what is <bg>


"Bob Phillips" <bob.ngs@xxxxxxxxxxxxx> wrote in message
news:OOV9Ka8GIHA.4880@xxxxxxxxxxxxxxxxxxxxxxx
By the cringe, you are a demanding use <bg>

Option Explicit

Dim prev
Private Const WS_RANGE As String = "I2" '<== change to suit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Not ValidatePostCode(.Value) Then
MsgBox "Invalid postcode, reverting to " & prev
.Value = prev
.Select
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
prev = Target.Value
End If
End Sub


--
HTH

Bob

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

"Leslie Isaacs" <leslie.isaacs@xxxxxxxxxxxxxxxx> wrote in message
news:%23v4O9T8GIHA.5584@xxxxxxxxxxxxxxxxxxxxxxx
Bob

Got it!
The only thing now is that, after editing a previously entered, valid
postcode, and making it invalid, the error is correctly generated but the
old (valid) postcode is lost. Is it possible for this to be retained? I
guess this would mean having a 'BeforeUpdate' event (in access terms,
anyway), so that the first value could be stored before it is overtyped.
This isn't critical but would be great if it's easy to achieve!

Thanks once more.
Les


"Bob Phillips" <bob.ngs@xxxxxxxxxxxxx> wrote in message
news:OStaV97GIHA.1184@xxxxxxxxxxxxxxxxxxxxxxx

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "I2" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Not ValidatePostCode(.Value) Then
MsgBox "Invalid postcode"
.Value = ""
.Select
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is work*** event code, which means that it needs to be
'placed in the appropriate work*** code module, not a standard
'code module. To do this, right-click on the *** tab, select
'the View Code option from the menu, and paste the code in.


I also changed the function a tad as this way lets a code of say BH152BB
(no space) through)

Function ValidatePostCode(ByVal PostCode As String) As Boolean
Dim Parts() As String
PostCode = UCase$(PostCode)
If InStr(PostCode, " ") > 0 Then
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]")))
Else
ValidatePostCode = False
End If
End Function

AX2 is no longer needed.

--
HTH

Bob

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

"Leslie Isaacs" <leslie.isaacs@xxxxxxxxxxxxxxxx> wrote in message
news:%233hD637GIHA.4916@xxxxxxxxxxxxxxxxxxxxxxx
Bob

I can't think why using event code wouldn't be acceptable - although I
don't know how to use it with excel.
I am familiar with the concept, because I use access a lot and have
lots of OnOpen, OnClose, OnCurrent etc etc etc events firing on the
appriopriate events. How do you do this with excel?

Thanks again
Les



"Bob Phillips" <bob.ngs@xxxxxxxxxxxxx> wrote in message
news:uHfhsV7GIHA.1208@xxxxxxxxxxxxxxxxxxxxxxx
Les,

I am seeing the problem as you see it.

I think it is a limitation of DV, so I would suggest using event code.

Is that acceptable?

Bob

"Les Isaacs" <leslie@xxxxxxxxxxxxxxxx> wrote in message
news:OrhmRTUGIHA.5160@xxxxxxxxxxxxxxxxxxxxxxx
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





















.

Loading