Private Const CelluleDate = "K74"
Private Sub Worksheet_Change(ByVal Target As Range)
Dim VarVal As Variant
Dim StrVal As String
If Intersect(Target, Me.Range(CelluleDate)) Is Nothing Then Exit Sub
'Pour éviter le dépassement de capacité sur un formt Date
Me.Range(CelluleDate).NumberFormatLocal = "Standard"
VarVal = Me.Range(CelluleDate).Value
If IsNumeric(VarVal) Then
StrVal = Trim(CStr(VarVal))
Application.EnableEvents = False
On Error Resume Next
Select Case Len(StrVal)
Case 6
Me.Range(CelluleDate).Value = CDate(Mid(StrVal, 1, 1) & "/" & Mid(StrVal, 2, 1) & "/" & Mid(StrVal, 3))
Case 7
If Left(StrVal, 1) = "0" Then
Me.Range(CelluleDate).Value = CDate(Mid(StrVal, 1, 2) & "/" & Mid(StrVal, 3, 1) & "/" & Mid(StrVal, 4))
Else
If CInt(Mid(StrVal, 2, 2)) <= 12 Then
Me.Range(CelluleDate).Value = CDate(Mid(StrVal, 1, 1) & "/" & Mid(StrVal, 2, 2) & "/" & Mid(StrVal, 4))
Else
Me.Range(CelluleDate).Value = CDate(Mid(StrVal, 1, 2) & "/" & Mid(StrVal, 3, 1) & "/" & Mid(StrVal, 4))
End If
End If
Case 8
Me.Range(CelluleDate).Value = CDate(Mid(StrVal, 1, 2) & "/" & Mid(StrVal, 3, 2) & "/" & Mid(StrVal, 5))
End Select
On Error GoTo 0
Application.EnableEvents = True
End If
End Sub