Validate Dates using VBA

[tdahlman]

I have a table ("Open Orders") with a column ("Date Paid"). If there is a date in the Date Paid column then I have a macro that removes that record from the table. What I want to do is use code that determines if any of the dates that have been entered are not equal to today's date. If they are all equal to today, then I want my current macro to continue to run and remove the record(s). If one or any of the dates are not equal to today, then I want a prompt that warns the user. I want the user to have the option to click continue or cancel. If the user clicks continue then I want the current code to continue running and then remove the records. If the user clicks cancel then the macro ends and he can check his entries.

I really appreciate any help on this.

Thanks
Travis

 

[VBA Geek]

if date paid is column D then u can do something like

Sub CheckDates()
Dim iRow As Integer
iRow = Range("D:D").Find("*", , , , xlByRows, xlPrevious).Row
If CBool(Evaluate(Replace("COUNTIF(D2:D@, ""<>"" & today())", "@", iRow))) Then
    If MsgBox("Do you wanna continue anyway?", vbYesNo) = vbYes Then Call mymacro Else: Exit Sub
Else
    Call mymacro
End If
End Sub


Sub mymacro()
' your main macro here
    MsgBox "hi"
End Sub

 

[tdahlman]

This is really close. Right now when I run this I get the message box regardless of which dates are entered. I think that the problem is most of the cells in the Date Paid column are blank, so it may be triggering the message box. I need to the code to ignore blank cells.

Thank you very much!
Travis

if date paid is column D then u can do something like

Sub CheckDates()
Dim iRow As Integer
iRow = Range("D:D").Find("*", , , , xlByRows, xlPrevious).Row
If CBool(Evaluate(Replace("COUNTIF(D2:D@, ""<>"" & today())", "@", iRow))) Then
    If MsgBox("Do you wanna continue anyway?", vbYesNo) = vbYes Then Call mymacro Else: Exit Sub
Else
    Call mymacro
End If
End Sub


Sub mymacro()
' your main macro here
    MsgBox "hi"
End Sub

 

[VBA Geek]

Sub CheckDates()
Dim iRow As Integer
iRow = Range("D:D").Find("*", , , , xlByRows, xlPrevious).Row
If CBool(Evaluate(Replace("COUNTIF(D2:D@,TODAY())<>ROWS(D2:D@)", "@", iRow))) Then
    If MsgBox("Do you wanna continue anyway?", vbYesNo) = vbYes Then Call mymacro Else: Exit Sub
Else
    Call mymacro
End If
End Sub