Data Validation Issue

  • Thread starter Thread starter Barry
  • Start date Start date
B

Barry

Excel 2003 under Windows XP SP2

I have two cells A20 and B20 both contain date info provided by a popup
calendar routine.
It is necessary for the date in B20 to be greater than that in A20,
otherwise calculations performed on the two cells provide negative numbers.

Any help would be GREATLY APPRECIATED! :)


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

On Error GoTo ws_exit
Application.EnableEvents = False

With Target
If Not Intersect(Target, Me.Range("A20")) Is Nothing Then
If .Offset(0, 1).Value <> "" Then
If .Value > .Offset(0, 1).Value Then
MsgBox "Invalid date"
.Value = ""
End If
End If
ElseIf Not Intersect(Target, Me.Range("B20")) Is Nothing Then
If .Value <> "" Then
If .Value <= .Offset(0, -1).Value Then
MsgBox "Invalid date"
.Value = ""
End If
End If
End If
End With

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.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Select B20
Use Data | Validation, and select Date from the Allow options
Now in the Setting tab specify: Date: Greater Than" Start date A20
best wishes
 
Bernard

Excel 2003 still allows a "less than" date to be entered when using the date
picker.

Manually entered dates will trigger the DV warning as advertised.


Gord Dibben MS Excel MVP
 
Oh dear, I missed the 'date picker' bit. I was imagining someone typing.
Must read questions more carefully!
thanks
 
You can either use the data/validation wizard to achieve this or
use vb to monitor this range

the wizard is easier
 
Well I tried Bob's approach but it still needs tweaking. If anyone wants to
look at the worksheet, let me know!


Thanks

Barry
 
Bob's approach works for me.

What "tweaking" do you think it needs?

Data Validation won't work with the date picker or a copied date.


Gord
 
I think his code was just what I was looking for. I was just confused as to
how to apply it to the rest of the worksheet. I will be happy to post the
sheet if anyone wants to give it a looksee! It's the only way I can explain.


Barry
 
Have downloaded and saved the workbook.

See you have adjusted target range but that doesn't create a problem in my
estimation.

If a bad date <date in A20:A23 is entered in B2:B23 you get the "Invalid Date"
message and date is not entered.

What tweaking do you specifically need doing?


Gord
 
GREAT BIG THANKS!!! To all who responded.

Bob Philips and Gord Dibben I hope you two realize what a super job you do
here on a regular basis.
I look foward to having another hickup for you guys to help me with....LOL


Barry
 
Back
Top