Yet more validation ... challenges!

[Les Isaacs]

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

 

[Bob Phillips]

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

 

[Les Isaacs]

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

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

_

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

_

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

 

[Bob Phillips]

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)

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

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

_

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

_

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

 

[Les Isaacs]

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,

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)

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

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

 

[Bob Phillips]

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

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,

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)

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

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

 

[Leslie Isaacs]

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,

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

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,

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)



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

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

 

[Bob Phillips]

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.

--
HTH

Bob

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

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,

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

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,

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)



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

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

 

[Leslie Isaacs]

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

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.

--
HTH

Bob

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

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,

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

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,

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)



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

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

 

[Bob Phillips]

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)

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

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.

--
HTH

Bob

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

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,

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

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,

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)



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

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

 

[Leslie Isaacs]

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>

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)

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

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.

--
HTH

Bob

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

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,

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

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,

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)



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

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

 

[Bob Phillips]

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

 

[Leslie Isaacs]

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]

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]

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

 

[Peo Sjoblom]

I didn't even know UK was 250 miles long? <vbg>


--


Regards,


Peo Sjoblom

 

[Les Isaacs]

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

 

[Bob Phillips]

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

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

 

[Bob Phillips]

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)