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