Date custom format (XP)

[AuntLinda1]

Want to format cells to display a 4/11/02 result WITHOUT the user having to type the forward slashes. Can this be done and if it can, how the heck to you do it???

Thanks

Linda

[LegareColeman]

If the dates are in column A, the code below placed in the worksheet change event routine should do that:

<pre>Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
Dim strVal As String
If Not Intersect(Target, ActiveSheet.Range("A:A")) Is Nothing Then
Application.EnableEvents = False
For Each oCell In Intersect(Target, ActiveSheet.Range("A:A"))
strVal = Format(oCell.Value, "000000")
oCell.Value = DateSerial(Right(strVal, 2), Mid(strVal, 3, 2), Left(strVal, 2))
Next oCell
Application.EnableEvents = True
End If
End Sub
</pre>

[AuntLinda1]

I'm the idiot you've helped B4 - apparently still having brain trouble - no clue where I'm supposed to write this routine.

Sorry -

[Andrew Cronnolly]

Linda,

Legare's code should go into the relevant worksheet codepane. To get to that just right click on th erelevant sheet tab, and select View Code. You can then post the code, but you will need to change the reference A:A (which is all column A) to the particular range you want this event to happen. The code is rigid in that any numeric value will be converted to a date, and nonnumeric entries or deleting existing entries will lead to errors.

As a partial remedy for that perhaps the following amendment to Legare's code might assist (hope he does not mind) :<pre>Dim oCell As Range
Dim strVal As String
If Not Intersect(Target, ActiveSheet.Range("A:A")) Is Nothing Then
Application.EnableEvents = False
For Each oCell In Intersect(Target, ActiveSheet.Range("A:A"))
If IsNumeric(oCell.Value) Then
strVal = Format(oCell.Value, "000000")
oCell.Value = DateSerial(Right(strVal, 2), _
Mid(strVal, 3, 2), Left(strVal, 2))
End If
Next oCell
Application.EnableEvents = True
End If
End Sub</pre>

Andrew C