-
Error Box help
I have a project that im working on and have lots of questions, but am trying to learn, so taking it one step at a time.
So, my question for today is: In 1 cell i can input a start date (1/1/2011) and in the cell below i can input the end date (12/31/2011) if the dates inputed are not a full year or are more than a year i want a message box to come up stating dates are incorrect.
I've am not sure how to start the macro (if this is the route i should take) to calculate the date difference which then would show the error message.
I am trying to learn, so, if someone could just help me get started, i will work from there.
-
Use a custom Data Validation formula of: =datedif(a1,a2,"y")>0
For more info about datedif see: http://www.cpearson.com/excel/datedif.aspx
-
Msgbox approach:
[vba]
Option Explicit
Sub DateCheck(d1 As Date, d2 As Date)
Const sModule As String = "DateCheck"
If Abs(d2 - d1) < 366 Then
Call MsgBox( _
"Sorry!!" & vbCrLf & vbCrLf & _
Format(d1, "short date") & " and " & Format(d2, "short date") & vbCrLf & vbCrLf & _
"are less than one year apart", vbCritical + vbOKOnly, sModule)
Else
Call MsgBox( _
"OK!!" & vbCrLf & vbCrLf & _
Format(d1, "short date") & " and " & Format(d2, "short date") & vbCrLf & vbCrLf & _
"are more than one year apart", vbCritical + vbOKOnly, sModule)
End If
End Sub
Sub test()
With ActiveSheet
.Range("A1") = #7/4/2011#
.Range("A2") = #8/4/2011#
Call DateCheck(.Range("A1"), .Range("A2"))
.Range("A2") = #7/4/2012#
Call DateCheck(.Range("A1"), .Range("A2"))
End With
End Sub
[/vba]
Paul
PS. Leap Years are left as a homework assignment :-)
-
thank you, i will mess with this and see how it goes.
thanks for the homework assignment
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules