Code to do edit checks when user moves out of a cell

[Eric @ BP-EVV]

I have a spreadsheet that the user inputs 4 dates (into 4 different cells)
where those dates are used in an SQL query against a database. Is there a way
to perform edit checks once the dates are input so as not to have the SQL
query execute until the dates are known to be valid ?

Base Period Start Date
Base Period End Date

Current Period Start Date
Current Period End Date

Obviously the start dates need to be before the respective end dates, but
also the Base Period needs to be prior to the Current Period. Also, no dates
can be blank, and none can be greater than yesterday.

Is there a way to execute a macro when each cell where these numbers are
input loses focus ... or am I better off with performing the total edit
checks once all 4 cells are filled in ?

 

[Barb Reinhardt]

I'd probably do it with a worksheet change event. SOmething like this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Dim ValidDate As Boolean

If Target.Count > 1 Then Exit Sub
' The 4 ranges can be changed and represent the cells with dates
Set myRange = Union(Range("A1"), Range("B2"), Range("C3"), Range("D4"))

ValidDate = False

If Intersect(Target, myRange) Is Nothing Then Exit Sub

'Code Check for valid dates. When date is valid set ValidDate = True

If Not ValidDate Then
Target.Select
MsgBox ("Date is not a valid date")
End If

End Sub

You'll have to put the code together to determine if the date is valid.