Re: Yet more validation ... challenges!

Help!

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

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

What do I do?

Thanks
Les

The current code:

Option Explicit

Dim prev
Private Const WS_RANGE As String = "I1:I999" '<== change to suit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

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


Const WS_RANGE As String = "V2:AW99"
Dim TimeStr As String

On Error GoTo EndMacro
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Application.Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & Mid(.Value, 2, 2) & _
":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & Mid(.Value, 3, 2) & _
":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
If Me.Cells(.Row, Me.Range(WS_RANGE).Columns(1).Column).Value <> ""
And Me.Cells(.Row, Me.Range(WS_RANGE).Columns(2).Column).Value <> "" And
Me.Cells(.Row, Me.Range(WS_RANGE).Columns(1).Column).Value > Me.Cells(.Row,
Me.Range(WS_RANGE).Columns(2).Column).Value Then
MsgBox "Start time earlier than end time"
.Value = ""
End If

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


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








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


--


Regards,


Peo Sjoblom


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


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

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

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

--
HTH

Bob

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

"Leslie Isaacs" <leslie.isaacs@xxxxxxxxxxxxxxxx> wrote in message
news:e$qNfd9GIHA.5276@xxxxxxxxxxxxxxxxxxxxxxx
Bob

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

Les



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

"Leslie Isaacs" <leslie.isaacs@xxxxxxxxxxxxxxxx> wrote in message
news:e8yI7v8GIHA.4880@xxxxxxxxxxxxxxxxxxxxxxx
Bob

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


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


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


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

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

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


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


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


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

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

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

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













.