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

 

[My Aswer Is This]

This script works for me.
You do realize the script is based on manually entering a date in Column(A)

It is not looking at a Table Range.

A Table is a Range on a Worksheet.

This script will be activate even if you enter a date in Range("A9000")
And Date means Todays Date
Which may be way below a Table Range

 

[NoSparks]

Your current code will leave events disabled if you delete an existing value in column A
try this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ans As Long
If Not Intersect(Target, Range("A:A")) Is Nothing Then
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
        Application.EnableEvents = False
        Target.Value = ""
        Application.EnableEvents = True
    End If
End If
End Sub

 

[dpaton05]

I am still learning to code so I don't fully understand it. I just found it on the net somewhere.

It works for me too but there are times that it won't work. I'm not sure of the specifics for when it won't work but I just know that sometimes it works and sometimes it doesn't.

When I get back to my computer, I will try that code you suggested.

Thanks

 

[dpaton05]

I put that code in and it didn't seem to work.

 

[My Aswer Is This]

Please explain what it is doing and is not doing.

Do you mean it is still working sometimes and not other times?

And are you saying you want it to work in just the table range

If so give the table name.

Or do you want it to work on any cell in column A

A Table is a range on a Worksheet.

 

[My Aswer Is This]

I asked this question which you never answered:

You do realize the script is based on manually entering a date in Column(A)​

 

[dpaton05]

do realize the script is based on manually entering a date in Column(A)[/FONT][/COLOR][/LEFT]

No I didn't know that as I didn't understand the code.

 

[dpaton05]

Please explain what it is doing and is not doing.

Do you mean it is still working sometimes and not other times?

And are you saying you want it to work in just the table range

If so give the table name.

Or do you want it to work on any cell in column A

A Table is a range on a Worksheet.

I enter a date in Column A of my table, npss_quote and the date is before today and there is meant to be a check if an older date is entered but it only sometimes is activated. I don't know why and there doesn't seem like there is any pattern for when it doesn't happen.

 

[My Aswer Is This]

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

No I didn't know that as I didn't understand the code.