Worksheet Change Code Won't Allow Cell To Be Cleared

[EnginerdUNH]

Hi,

I have a workbook with one sheet where the user will enter a date into cell D11 and then the workbook checks the date entered to make sure it's in the correct format. The worksheet_change event works as follows:
1) check the date entered to make sure it's in proper date format. if the date contains any ".", replace them with "/"
2) check the year entered to make sure it's the same as the year stored in cell D1. If it's different, change it.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Application.EnableEvents = False
    If Not (Intersect(Target, Range("D11")) Is Nothing) Then
        Target.Value = Replace(Target.Value, ".", "/")
        If Year(Target.Value) <> Range("D1").Value Then
            temp = Target.Value
            Target.Value = DateSerial(Range("D1").Value, Month(temp), Day(temp))
        End If
    End If
    Application.EnableEvents = True
End Sub

The problem that I'm running into now is that any time the user goes to clear the cell after they're done, the cell defaults to a value of 12/30/2023 rather than having nothing like it should. I'm hoping someone has a helpful fix that will allow the user to clear the cell.

[YasserKhalil]

Maybe

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim temp
    On Error Resume Next
    If Not (Intersect(Target, Range("D11")) Is Nothing) Then
        Application.EnableEvents = False
            If Target.Value = Empty Then
            Else
                Target.Value = Replace(Target.Value, ".", "/")
                If Year(Target.Value) <> Range("D1").Value Then
                    temp = Target.Value
                    Target.Value = DateSerial(Range("D1").Value, Month(temp), Day(temp))
                End If
            End If
        Application.EnableEvents = True
    End If
End Sub

[HansV]

Or slightly shorter: change

Code: Select all

            If Target.Value = Empty Then
            Else
 

to

Code: Select all

            If Target.Value <> "" Then

(No Else)

[EnginerdUNH]

thank you YasserKhalil and Hans, ended up changing

Code: Select all

If Not (Intersect(Target, Range("D11")) Is Nothing) Then

to

Code: Select all

If Not (Intersect(Target, Range("D11")) Is Nothing) And Target.Value <> "" Then

and it worked perfectly

[snb]

Code: Select all

If Target.address = "$D$11" And Target <> "" Then