What I'm doing is to allow the user to put in quick dates (without the
slashes) or dates (with slashes). The below works except for when a date is
entered (example cell C4) and then they decide that they put in a wrong date
and if they use the quick date (the logic is assuming that the "date" format
is being used (with slashes) Example the first time information is coded in
A4 the user enters 31508 the VBA will make this 3/15/2008. Then if the user
enters 31408 the VBA will make this 12/27/1985. However if they deleted the
entry first and then entered 31408 the desired result of 3/14/08 would show
up.


Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim DateStr As String

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
If Target.Value = "" Then
Target.NumberFormat = "General"
Exit Sub
End If
If VarType(Target) = vbDate Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Formula)
Case 4 ' e.g., 9298 = 2-Sep-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 1) & "/" & Right(.Formula, 2)
Case 5 ' e.g., 11298 = 12-Jan-1998 NOT 2-Nov-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 2) & "/" & Right(.Formula, 2)
Case 6 ' e.g., 090298 = 2-Sep-1998
DateStr = Left(.Formula, 2) & "/" & _
Mid(.Formula, 3, 2) & "/" & Right(.Formula, 2)
Case 7 ' e.g., 1231998 = 23-Jan-1998 NOT 3-Dec-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 2) & "/" & Right(.Formula, 4)
Case 8 ' e.g., 09021998 = 2-Sep-1998
DateStr = Left(.Formula, 2) & "/" & _
Mid(.Formula, 3, 2) & "/" & Right(.Formula, 4)
Case Else
Err.Raise 0
End Select
.Formula = DateValue(DateStr)
End If

End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid date."
Application.EnableEvents = True
End Sub