Re: Yet more validation ... challenges!

Bob

The NI number is validated using data validation, not code. The DV formula
is
=AND(LEN(L14)=9,ISNUMBER(MATCH(LEFT(L14,2),VALID_FIRST,0)),ISNUMBER(MATCH(RIGHT(L14,1),VALID_LAST,0)),ISNUMBER(--MID(L14,3,5)))
Valid_first and valid_last are lists in sheet2.

As you say, hitting delete on a valid postcode does generate the error
message, which it shouldn't, but at least it then restores the valid
postcode. Also, curiously, this does not cause the latest problem where
hitting the delete key seems to stop the rest of the validation code from
working.

To get the error, I just have to open the workbook and either immediately or
at any time I hit the delete key while the active cell is anywhere EXCEPT IN
THE NI NUMBER COLUMN (column L)!!!!

To be honest, coming to it this morning I really wondered whether I had
dreamt it all: unfortunately not! I really thought I was getting somewhere
with my understanding of excel coding, but this completely defeats me. Hope
you can see what's going on: or doesn't it happen south of Watford?

Thanks again
Les


"Bob Phillips" <bob.NGs@xxxxxxxxxxxxx> wrote in message
news:ukgwBLBHIHA.280@xxxxxxxxxxxxxxxxxxxxxxx
Where is the NI in this Les. There is postcode validation code, and time
processing/checking, but I see no NI checks.

And what is the active cell when you hit delete? I hit delete on a valid
postcode and it errors for me (I assume it shouldn't), and restores the
old postcode.

Can you give a cell by cell description of the steps you are making that
cause the error?

--
---
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:OxgymeAHIHA.4296@xxxxxxxxxxxxxxxxxxxxxxx
Bob

Thanks for that - problem fixed ... BUT:
This just goes on and on ...
Now I have a very strange situation in that on first opening the workbook
everything works OK - times, NI and Postcode validation, but as soon as I
hit the delete key, or on entering an invalid NI number (which is
correctly rejected), all the code seems to stop working! i.e. from that
point on I can enter any value in the time columns, or the postcode
column, and nothing gets rejected!!!!!!!!!
When I close the workbook, whether or not I save it, on opening it again
I again get the strange behaviour described above.
Is it me, or is this data input *** just not meant to be?
I hope you like a challenge - and that you don't think I'm making this
up.

As ever ...
Les


"Bob Phillips" <bob.NGs@xxxxxxxxxxxxx> wrote in message
news:OoPVXt$GIHA.5208@xxxxxxxxxxxxxxxxxxxxxxx
This should do it Les.



Dim prev
Private Const WS_RANGE_PC As String = "I1:I999"
Private Const WS_RANGE_TIME As String = "V2:AW99"

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

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE_PC)) Is Nothing Then
With Target
If Not ValidatePostCode(.Value) Then
If prev = "" Then
MsgBox "Invalid postcode."
Else
MsgBox "Invalid postcode, reverting to " & prev
End If
.Value = prev
.Select
End If
.Value = UCase(.Value)
End With
Else
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_TIME)) 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_TIME).Columns(1).Column).Value <> "" _
And Me.Cells(.Row,
Me.Range(WS_RANGE_TIME).Columns(2).Column).Value <> "" And _
Me.Cells(.Row,
Me.Range(WS_RANGE_TIME).Columns(1).Column).Value
_
Me.Cells(.Row,
Me.Range(WS_RANGE_TIME).Columns(2).Column).Value Then
MsgBox "Start time earlier than end time"
.Value = ""
End If
End With
End If

Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time. Do not use colons etc. - " &
_
"enter 8.00am as 800, enter 4.30pm as 1630 etc."
Target.Value = ""
ws_exit:
Application.EnableEvents = True
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Me.Range(WS_RANGE_PC)) 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)



"Les Isaacs" <leslie@xxxxxxxxxxxxxxxx> wrote in message
news:OtTSYI$GIHA.6068@xxxxxxxxxxxxxxxxxxxxxxx
Help!

I had everything working on my test workbook, but when I have come to
add the new code to the main workbook I find that it conflicts with the
other code that is already there (the other code is used to validate
times). I have tried various things but am obviously out of my depth!

The code I now have is pasted below, but with it like this I get a
message "Compile error - Duplicate declaration in current scope" and I
am taken to the line
Const WS_RANGE As String = "V2:AW99"

What do I do?

Thanks
Les

The current code:

Option Explicit

Dim prev
Private Const WS_RANGE As String = "I1:I999" '<== 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
If prev = "" Then
MsgBox "Invalid postcode."
Else
MsgBox "Invalid postcode, reverting to " & prev
End If
.Value = prev
.Select
End If
.Value = UCase(.Value)
End With
End If


Const WS_RANGE As String = "V2:AW99"
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. Do not use colons etc. -
enter 8.00am as 800, enter 4.30pm as 1630 etc."
Target.Value = ""
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








"Peo Sjoblom" <terre08@xxxxxxxx> wrote in message
news:%23kVRTM%23GIHA.3548@xxxxxxxxxxxxxxxxxxxxxxx
I didn't even know UK was 250 miles long? <vbg>


--


Regards,


Peo Sjoblom


"Leslie Isaacs" <leslie.isaacs@xxxxxxxxxxxxxxxx> wrote in message
news:eGDk649GIHA.5584@xxxxxxxxxxxxxxxxxxxxxxx
Dorset ... Aus ... NZ - anywhere south of Watford and it's all the
same to us northerners!
But next time I hear 'By the cringe' I'll know what I'm dealing with.
Les


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

No mate, you are way off! My daughter is in NZ at this moment, but I
personally have never been to either, or even south of the equator.

I am 250 miles south of you, in Dorset, by the glorious Jurassic
coastline. I thought that 'By the cringe' would have given it a way.

--
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:e$qNfd9GIHA.5276@xxxxxxxxxxxxxxxxxxxxxxx
Bob

Sounds good to me: wonder when I'll be posting answers myself!
Where are you, BTW. I cannot find any identifying nationalistic
nuances in your posts, but my guess would be Australia or New
Zealand. Am I in the right hemisphere?

Les



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

"Leslie Isaacs" <leslie.isaacs@xxxxxxxxxxxxxxxx> wrote in message
news:e8yI7v8GIHA.4880@xxxxxxxxxxxxxxxxxxxxxxx
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.


We all do one way or another Les. I got a bit of help when I
started back, and I now have a network of people I can call on for
specific problems (no-one minds as long as it isn't abused), and
there are always other topics that I don't know as well.


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?


I can only speak for myself, althoughI bet the rest are much the
same, but I do it for fun. It is good to see the sort of problems
people really encounter, and help them solve it. And it is nice
getting 'to know' someone over the groups, I have even met up with
some of them at events and the like. I haven't gotten anything in
the way of consultancy from the groups, I get far more by
recommendation or from meeting people at seminars, conferences and
the like, but it doesn't hurt to have something tangible to show
people to backup my claims of myself <g>.

And of course, we learn ourselves by following posts. Only
yesterday, I found a way of linking a textbox on a *** to a
cell, soemthing I wasn't aware of. It is symbiotic.

And of course, there is tons of free stuff available on the net
that I avail myself of regularly. I guess I intrinsically believe
that we should do these things for free, give and get.


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


The net can good and bad. I agree, this is one of its finer
manifestations.


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

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

The <bg> is net shorthand for big grin, seeing as we can't add
smileys to the NGs. It was my way of saying, I am only joking in
the preceding statement, or that you aren't pushing the the
boundary of goodwill too far.

Others are <g> for grin, <ebg> for extra big grin or even bigger
grin, plus all the usual acronymns like ROFL, LOL, etc.





















.