Private Sub Worksheet_Change(ByVal Target As Range)
'--------- adaptation de la proposition de C.Pearson
'----------http://cpearson.com/excel/DateTimeEntry.htm
Dim J As Integer, M As Integer, A As Integer
On Error GoTo EndMacro
If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
Target.NumberFormat = "General"
Application.EnableEvents = False
'With Target
If Target.HasFormula = False Then
Select Case Len(Target.Formula)
Case 4 ' e.g., 2998 = 2-Sep-1998 j m aa
J = Left(Target.Formula, 1): M = Mid(Target.Formula, 2, 1): A = Right(Target.Formula, 2)
Case 5 ' e.g., 12198 = 12-Jan-1998 jj m aa
J = Left(Target.Formula, 2): M = Mid(Target.Formula, 3, 1): A = Right(Target.Formula, 2)
Case 6 ' e.g., 090298 = 2-Sep-1998 jj mm aa
J = Left(Target.Formula, 2): M = Mid(Target.Formula, 3, 2): A = Right(Target.Formula, 2)
Case 7 ' e.g., 1231998 = 12-Mar-1998 jj m aaaa
J = Left(Target.Formula, 2): M = Mid(Target.Formula, 3, 1): A = Right(Target.Formula, 4)
Case 8 ' e.g., 09021998 = 2-Sep-1998
J = Left(Target.Formula, 2): M = Mid(Target.Formula, 3, 2): A = Right(Target.Formula, 4)
Case Else
Target.NumberFormat = "General"
Err.Raise 1
Exit Sub
End Select
Target.Value = DateValue(J & "/" & M & "/" & A)
End If
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox Target.Address & " : " & Target.Formula & Chr(10) & "Date saisie non valide !" & Chr(10) & "4car : j m aa" & Chr(10) & "5car : jj m aa" & Chr(10) & "6car : jj mm aa" & Chr(10) & "7car : jj m aaaa" & Chr(10) & "8car : jj mm aaaa", vbCritical
Target.Select
Application.EnableEvents = True
End Sub