PDA

View Full Version : Error Box help



CloudenL
11-30-2011, 08:39 AM
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.

Kenneth Hobs
11-30-2011, 09:20 AM
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

Paul_Hossler
11-30-2011, 09:27 AM
Msgbox approach:


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


Paul

PS. Leap Years are left as a homework assignment :-)

CloudenL
11-30-2011, 09:39 AM
thank you, i will mess with this and see how it goes.

thanks for the homework assignment :)