I'm trying to create A Wsheet change event that will change the format of a date entered in col D from dd/mm/yy to dd.mm
My code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Target.HasFormula Then
Target.NumberFormat = "dd.mm"
Application.EnableEvents = True
End If
End Sub
However, when I test it nothing happens. What am I missing?
Thanks!
Your code is close, but there are a few issues preventing it from working as intended. Here's a revised version. Place this in the Worksheet you are editing.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Or Target.Cells.Count > 1 Then
Exit Sub
End If
Application.EnableEvents = False
On Error GoTo ErrorHandler
If Not Target.HasFormula Then
If IsDate(Target.Value) Then
Target.NumberFormat = "dd.mm"
Target.Value = Target.Value ' Trigger recalculation
End If
End If
ExitSub:
Application.EnableEvents = True
Exit Sub
ErrorHandler:
MsgBox "An error occurred: " & Err.Description
Resume ExitSub
End Sub
Thanks mate!
