Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / New Users / November 2007

Tip: Looking for answers? Try searching our database.

Yet more validation ... challenges!

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Les Isaacs - 26 Oct 2007 19:34 GMT
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?

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.

Just when you thought you'd heard the last from me!!

Thanks for your help (again).
Les

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 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
       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. - enter
8.00am as 800, enter 4.30pm as 1630 etc."
   Application.EnableEvents = True
End Sub
Reply to this Message
Bob Phillips - 26 Oct 2007 23:08 GMT
> Hello All
>
[quoted text clipped - 10 lines]
> 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
[quoted text clipped - 5 lines]
> 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 worksheet 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
Reply to this Message
Les Isaacs - 27 Oct 2007 09:40 GMT
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

>> Hello All
>>
[quoted text clipped - 107 lines]
>               Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#[0-9ABEHMNPRVWXY]")))
> End Function
Reply to this Message
Bob Phillips - 27 Oct 2007 23:21 GMT
Les,

Try this.

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

Signature

---
HTH

Bob

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

> Bob
>
[quoted text clipped - 141 lines]
>>               Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#[0-9ABEHMNPRVWXY]")))
>> End Function
Reply to this Message
Les Isaacs - 28 Oct 2007 10:28 GMT
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

> Les,
>
[quoted text clipped - 150 lines]
>>>               Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#[0-9ABEHMNPRVWXY]")))
>>> End Function
Reply to this Message
Bob Phillips - 31 Oct 2007 12:59 GMT
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

> Bob
>
[quoted text clipped - 176 lines]
>>>>               Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#[0-9ABEHMNPRVWXY]")))
>>>> End Function
Reply to this Message
Leslie Isaacs - 31 Oct 2007 14:00 GMT
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

> Les,
>
[quoted text clipped - 188 lines]
>>>>>               Parts(0) Like "[A-PR-UWYZ][A-HK-Y]#[0-9ABEHMNPRVWXY]")))
>>>>> End Function
Reply to this Message
Bob Phillips - 31 Oct 2007 14:10 GMT
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 worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet 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.

Signature

HTH

Bob

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

> Bob
>
[quoted text clipped - 201 lines]
>>>>>> "[A-PR-UWYZ][A-HK-Y]#[0-9ABEHMNPRVWXY]")))
>>>>>> End Function
Reply to this Message
Leslie Isaacs - 31 Oct 2007 14:50 GMT
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

> Private Sub Worksheet_Change(ByVal Target As Range)
> Const WS_RANGE As String = "I2"     '<== change to suit
[quoted text clipped - 254 lines]
>>>>>>> "[A-PR-UWYZ][A-HK-Y]#[0-9ABEHMNPRVWXY]")))
>>>>>>> End Function
Reply to this Message
Bob Phillips - 31 Oct 2007 15:01 GMT
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

Signature

HTH

Bob

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

> Bob
>
[quoted text clipped - 267 lines]
>>>>>>>> "[A-PR-UWYZ][A-HK-Y]#[0-9ABEHMNPRVWXY]")))
>>>>>>>> End Function
Reply to this Message
Leslie Isaacs - 31 Oct 2007 15:40 GMT
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>

> By the cringe, you are a demanding use <bg>
>
[quoted text clipped - 299 lines]
>>>>>>>>> "[A-PR-UWYZ][A-HK-Y]#[0-9ABEHMNPRVWXY]")))
>>>>>>>>> End Function
Reply to this Message
Bob Phillips - 31 Oct 2007 15:59 GMT
> Bob
>
[quoted text clipped - 3 lines]
> 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 sheet 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.
Reply to this Message
Leslie Isaacs - 31 Oct 2007 17:01 GMT
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
>>
[quoted text clipped - 49 lines]
> Others are <g> for grin, <ebg> for extra big grin or even bigger grin,
> plus all the usual acronymns like ROFL, LOL, etc.
Reply to this Message
Bob Phillips - 31 Oct 2007 17:31 GMT
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.

Signature

HTH

Bob

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

> Bob
>
[quoted text clipped - 58 lines]
>> Others are <g> for grin, <ebg> for extra big grin or even bigger grin,
>> plus all the usual acronymns like ROFL, LOL, etc.
Reply to this Message
Leslie Isaacs - 31 Oct 2007 17:51 GMT
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

> Les,
>
[quoted text clipped - 66 lines]
>>> Others are <g> for grin, <ebg> for extra big grin or even bigger grin,
>>> plus all the usual acronymns like ROFL, LOL, etc.
Reply to this Message
Peo Sjoblom - 31 Oct 2007 18:25 GMT
I didn't even know UK was 250 miles long? <vbg>

Signature

Regards,

Peo Sjoblom

> Dorset ... Aus ... NZ - anywhere south of Watford and it's all the same to
> us northerners!
[quoted text clipped - 72 lines]
>>>> Others are <g> for grin, <ebg> for extra big grin or even bigger grin,
>>>> plus all the usual acronymns like ROFL, LOL, etc.
Reply to this Message
Les Isaacs - 31 Oct 2007 20:14 GMT
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

>I didn't even know UK was 250 miles long? <vbg>
>
[quoted text clipped - 74 lines]
>>>>> Others are <g> for grin, <ebg> for extra big grin or even bigger grin,
>>>>> plus all the usual acronymns like ROFL, LOL, etc.
Reply to this Message
Bob Phillips - 31 Oct 2007 21:19 GMT
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

Signature

---
HTH

Bob

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

> Help!
>
[quoted text clipped - 176 lines]
>>>>>> Others are <g> for grin, <ebg> for extra big grin or even bigger
>>>>>> grin, plus all the usual acronymns like ROFL, LOL, etc.
Reply to this Message
Les Isaacs - 31 Oct 2007 22:48 GMT
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 sheet 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

> This should do it Les.
>
[quoted text clipped - 267 lines]
>>>>>>> Others are <g> for grin, <ebg> for extra big grin or even bigger
>>>>>>> grin, plus all the usual acronymns like ROFL, LOL, etc.
Reply to this Message
Bob Phillips - 01 Nov 2007 00:06 GMT
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?

Signature

---
HTH

Bob

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

> Bob
>
[quoted text clipped - 287 lines]
>>>>>>>> Others are <g> for grin, <ebg> for extra big grin or even bigger
>>>>>>>> grin, plus all the usual acronymns like ROFL, LOL, etc.
Reply to this Message
Les Isaacs - 01 Nov 2007 08:47 GMT
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

> Where is the NI in this Les. There is postcode validation code, and time
> processing/checking, but I see no NI checks.
[quoted text clipped - 301 lines]
>>>>>>>>> Others are <g> for grin, <ebg> for extra big grin or even bigger
>>>>>>>>> grin, plus all the usual acronymns like ROFL, LOL, etc.
Reply to this Message
Bob Phillips - 01 Nov 2007 09:35 GMT
> 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.

So really, this is incidental to the problem?

> 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.

Do you want it that the code will allow a delete to clear out a cell and
remove the postcode without an error?

> 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)!!!!

I didn't get that error in my test. I will re-try it.

> 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?

Whilst I will test it again,  it doesn't happen down here.

Also the sun always shines down here; the women are all beautiful; and, all
the kids are well behaved. They call it the north-south divide.
Reply to this Message
Les Isaacs - 01 Nov 2007 10:37 GMT
Bob

> So really, this is incidental to the problem?
Yes (sorry!).

> Do you want it that the code will allow a delete to clear out a cell and
> remove the postcode without an error?
Yes (please!)

> I didn't get that error in my test. I will re-try it.
It is entirely consistent here.

> Whilst I will test it again,  it doesn't happen down here.
Shall I email you my workbook?

> Also the sun always shines down here; the women are all beautiful; and,
> all the kids are well behaved. They call it the north-south divide.
I'm on my way!

Les
Reply to this Message
Bob Phillips - 01 Nov 2007 10:41 GMT
Les,

Send it over, that will cut to the quick

bob dot phillips at tiscali dot co dot uk

Signature

HTH

Bob

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

> Bob
>
[quoted text clipped - 16 lines]
>
> Les
Reply to this Message
Bob Phillips - 02 Nov 2007 11:40 GMT
Not received anything yet Les.

Signature

HTH

Bob

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

> Les,
>
[quoted text clipped - 22 lines]
>>
>> Les
Reply to this Message
Leslie Isaacs - 02 Nov 2007 15:45 GMT
Bob

That's frustrating: in fact I checked last night and saw that what I had
sent yesterday moring was to the wrong address (one 'l' in phillips), so I
sent it again with the right address - at around 11.30pm. I'm in the other
office now, but will check again and resend this evening.

Thanks for bearing with me.
Les

> Not received anything yet Les.
>
[quoted text clipped - 24 lines]
>>>
>>> Les
Reply to this Message
Bob Phillips - 02 Nov 2007 22:56 GMT
It's okay, the second got caught in the spam filter. I have it now.

Signature

---
HTH

Bob

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

> Bob
>
[quoted text clipped - 35 lines]
>>>>
>>>> Les
Reply to this Message
Bob Phillips - 02 Nov 2007 23:18 GMT
... but I cannot reproduce the problem Les.

I enter an invalid postcode - it errors. I enter another invalid code, it
errors. If I select something else and come back, it still errors.

This is very odd.

Signature

---
HTH

Bob

Reply to this Message

 Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.