Help with VBA code

[dpaton05]

I want to have a message box pop up if the date entered in column A of a table row is earlier than todays date, I have some code but it only seems to sometimes work. What is wrong with it or is there better code? The table's name is npss_quote

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ans As Long
      If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
        If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
        If Target.Value < Date Then
            ans = MsgBox("This input is older than today !....Are you sure that is what you want ???", vbYesNo)
        If ans = vbNo Then Target.Value = ""
    End If
End If
Application.EnableEvents = True

End Sub

 

[dpaton05]

So is that what you want? If not explain what you do want.

I want a message box to appear if a date entered is earlier then today's date, asking for confirmation. But for it to occur with every early date that is entered.

 

[My Aswer Is This]

Well you posted a script you found.

The script you found activates when you enter a date in column A
It checks that particular date you just entered.
It does not check all the dates previously entered.

 

[NoSparks]

try this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ans As Long, rng As Range
Set rng = ActiveSheet.ListObjects("npss_quote").ListColumns(1).Range
If Not Intersect(Target, rng) Is Nothing Then
    If Target.Count <> 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Value < Date Then
        ans = MsgBox("This input is older than today !....Are you sure that is what you want ???", vbYesNo)
        If ans = vbNo Then
            Application.EnableEvents = False
            Target.Value = ""
            Application.EnableEvents = True
        End If
    End If
End If
End Sub