A msg box only sometimes alerts on earlier date entered

[dpaton05]

I have a table and the first column for a row is the date. If a date is entered that is earlier then today's date, a message box should appear and ask you if that is what you want. It is just to add an extra layer of protection against typos. If a new row is added then a date entered that is earlier than today, the message box will appear but if you open the worksheet and straight away enter a date without adding any new rows, the message box will not appear. What do I need to change so that the message box will appear regardless?

Thanks,
Dave


I think this is the part that needs to be changed:

Private Sub Worksheet_Change(ByVal Target As Range)
'Worksheets("Costing_tool").Unprotect
Dim ans As String
    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
'Worksheets("Costing_tool").Protect
End Sub

 

[dpaton05]

I commented out the line to exit the sub and added another line that turned on EnableEvents before exiting.

        Select Case Target.Column
            Case Is = 1
                'If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
                If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then
                    Application.EnableEvents = True
                    Exit Sub
                End If

Thanks for your help Peter

 

[Peter_SSs]

Thanks for your help Peter

You're welcome.

I'm not going to try to re-write your whole code for you, but perhaps a more logical approach would be to disable events in the 'Delete All Lines' macro before it actually deletes the lines then the Worksheet_Change code would not even be called in the first place when deleting lines. After all, if you are deleting lines there should be no dates in the changed area for the Worksheet_Change code to check. Worth having a think about.

BTW, If you follow my suggestion don't forget to re-enable events before you leave the 'Delete All Lines' macro.