Input format

[Brad]

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

 

[Gord Dibben]

Looks like Chip Pearson's code.

But you added this to allow entering with slashes.

If VarType(Target) = vbDate Then
Exit Sub

Since the 31508 first entered was formatted as date, the subsequent 31408
Exits the Sub because you already have a date entered..........so 31408 goes
in as a serial number which is 12/27/1985

Remove those lines and you will be OK

Instruct users not to enter slashes.


Gord Dibben MS Excel MVP

 

[Brad]

It is Chip's Pearson's code, with some small modifications. If I have to
allow for either (with or without slashes) at any time, any recommendations?

 

[Gord Dibben]

I tried working around it before posting earlier but with my limited VBA
skills could not find a way to prevent the results you posted originally.

That's why I posted these suggestions.

Hang in there............someone will show you a way.


Gord