Solved: Onchange event to check a date

[andrewvanmar]

I have several fields that need to have a date entered ( i do this through a datepicker).
I want to make sure that one date is today or in the future, and the other atleast tomorow or in the furure.

I found this: [VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("c5"), Target) Is Nothing Then
MsgBox "c5 has changed"
End If
End Sub[/VBA]

Which makes a mssg apear if the value changes.

Now I want to change this code so it check the value itself (a date) and if it's not now or later, it should clear that same field and generate a message (so they have to try again).

possible?

[rory]

You could use something like:
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCheck As Range
Set rngCheck = Range("c5")
If Not Intersect(rngCheck, Target) Is Nothing Then
With rngCheck
If Len(.Value) > 0 Then
If .Value < Date Or Not IsDate(.Value) Then
On Error Resume Next
Application.EnableEvents = False
.ClearContents
.Select
Application.EnableEvents = True
MsgBox .Address(0, 0) & " must be today or later!"
End If
End If
End With
End If
End Sub
[/VBA]

Regards,
Rory

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("c5"), Target) Is Nothing Then
If IsDate(taget) Then
If Target.Value >= Date Then
MsgBox "Datae in future"
Target.ClearContents
End If
End If
End If
End Sub
[/vba]

[andrewvanmar]

I get on both instance of the code an error pointing to date, that it can't find the project or library. but if I change the word date to now, it works.

Now I wonder ( for the second cell) wuould it work for tomorrow rtoo?

[Bob Phillips]

Yes, but testing for tomorrow's date.

Actually now might be a problem, because today's date will fail (NOW > Date by the hous and mins fraction).

Check that you don't have missing references.

[rory]

If you select Tools-References from the menu in the VB Editor, do any of the selected references start with 'MISSING:'? Date is a built-in function and should always work. For tomorrow, you just test for >= Date + 1
Regards,
Rory

[andrewvanmar]

Hmm, strange, since there is nothing marked missing in the reference library.

EDIT:
I had eurotool.xla missing, I unchecked it to see what happens

[andrewvanmar]

YUP it works!!

[andrewvanmar]

erm, well almost... if I enter today's date it triggers, while it should only trigger with yesterday and before...

ah tthats because of the <= and >= once I remove the = it works!


Thanks guys!!

[Bob Phillips]

For tomorrow, you just test for >= Date + 1

or > Date?

[andrewvanmar]

no for tomorrow it's < date + 1 for today it's < date

= if the date is smaller than today then mssg and clear
and if the date is smaller than today +1 then mssg and clear

[rory]

> Date works if there's no chance they enter a time; otherwise, >= Date + 1 is safer in my book.
Or less than, as seems to be the case here!
Regards,
Rory

[andrewvanmar]

they will enter from a datepicker so I think it's safe.

if you use >= and <= you exclude toda?'s day from the possibilities:

ex.
If Target.Value <= Date Then
MsgBox "Data in future
if value is less or equal than today give message. Which means that today is wrong too

which would require a <=date - 1 for today, and <= date for tomorrow.