How do I check if date is grater than... 1

[JrClown]

Hi all, I need VBA code help.
Sample:
In Excel in cell A1 I have this date 1/16/01
and in cell A2 I have this date 1/17/01

I want to prompt the user if he\she types a greater date in A1 than A2 with a pop-up MsgBox "Date in A1 can not be greater than A2"


Thanks for you help guys

"The reward of one duty done is the power to fulfill another"
<%
Jr_Clown@Yahoo.com
%>
​

 

[SkipVought]

Hi,
Each Worksheet Object has Events in which you can trigger code and stuff. ;-)

Well, this sounds like a Worksheet_Change Event.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Date1 As Date, Date2 As Date
    With Target
        If .Row <= 2 Then Exit Sub
        Date2 = Cells(.Row, 1).Value
        Date1 = Cells(.Row - 1, 1).Value
        If Date1 > Date2 Then
            MsgBox &quot;Date problem&quot;
        End If
    End With
End Sub

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[TonyU]

Thanks for your response, but I have a couple of questions seen in red.
I've pasted the code in my workbook and typed numbers
5 in A1 &
1 in A2
Obviously I'm not using the code properly. Can you please explain to me how I use this code in reference to my cells? Thank you very much.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Date1 As Date, Date2 As Date
With Target
If .Row <= 2 Then Exit Sub
Date2 = Cells(.Row, 1).Value
Date1 = Cells(.Row - 1, 1).Value
If Date1 > Date2 Then
MsgBox &quot;Date problem&quot;
End If
End With
End Sub
Tony

 

[SkipVought]

Hi,
I have made an assumption based on certain Best Practices...
And that assumption was that when there is a table of data (columns of similar data ie column A is Date...) then Row 1 will contain a heading that describes the data and the actual data will begin in Row 2.
So that's how it's coded.

If you want you data to begin in Row 1, then change

If .Row <= 2 Then Exit Sub

to be

If .Row <=1 Then Exit Sub

I may have misunderstood your request, because I coded it so that you could enter Dates clear down to the last Row and it would check to see if it is greater than the previous Row. Skip,
metzgsk@voughtaircraft.com

 

[TonyU]

My mistake. I should've structured my question a little better.

What am doing is creating this automated form in excel 2000 and there just happens to be two dates that I would like to check and prompt the user is they type an error (one greater than the other) and the dates at any giving time on any form could end up anywhere I choose to place them.
I guess what's needed here is a code that I could costumized to check any two dates whenever this situation arises. I appreciate you help in this matter. Tony

 

[TonyU]

Perhaps I should've started my own question instead of using JrClown's one. I apologize JrClown for using your question but I'm also having that problem. Tony

 

[SkipVought]

Well you can write a function to do that. It returns TRUE of FALSE.

Function DateCheck(ByVal Date1 As Date, ByVal Date2 As Date) As Boolean
    If Date1 > Date2 Then
        DateCheck = False
    Else
        DateCheck = True
    End If
End Function

So you could us the function in code like this...

    If DateCheck(Range("A1"), Range("A2")) Then
        'dates check out
    Else
        'TILT!
    End If

Hope this helps. Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

What I should add, being that this is being related to sheets and forms (as well as other Date-related stuff) here are some additional thoughts...

1. When you are trying to &quot;dummy proof&quot; an application, you want to check data that the user enters... like Date data. So you ought to use the function, IsDate, to validate that the data is valid.

2. In a form, you would use the TextBoxn.Value property for the approreiate Dates.

Hope this helps. Skip,
metzgsk@voughtaircraft.com

 

[TonyU]

I need to read more about VBA I guess, this is not english to me. Thanks for the input tho. Tony

 

[TonyU]

Can you suggest a good\great beginner's VBA book Sir? Thanks Tony

 

[SkipVought]

Walkenbach has several. I like his work. Skip,
metzgsk@voughtaircraft.com

 

[TonyU]

tyvm Tony