Hi Norman

like your solution but I wasn't satisfied with the small drawbacks
(re-entry of 6 digits or leading zeros). So I Changed the
selection_Change event as posted below:
- included a static variable for the previous selection
- ALWAYS change the format of the selected cell to 'Text'
- BUT restore the date format again after the selection has left the
filled cell.

One drawback: The user sees the conversion to a serial date number if
he selects a filled date cell. No idea how to prevent this.
Waiting for your' (and Bob's) comments <vbg

------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'use a static variable to store the old selection.
'Used to restore the date format after a the selected cell with a
'value has been changed to text format
Static OldSelection As Range

'Restore the date format for filled cells. Disable events to prevent
'triggering the worksheet_change event
If Not OldSelection Is Nothing Then
With OldSelection
If .Value < "" Then
Application.EnableEvents = False
.NumberFormat = "dd-mmm-yyyy"
.Value = .Value
Application.EnableEvents = True
End If
End With
End If

'Object here is to format as text as soon as selection is made.
'I'll change to a date format when I've parsed the entry.
'This avoids leading zero and other inadmissible date probs.

'Usual exit if not in my range
If Application.Intersect(Target, Range(TestRange)) Is Nothing Then
Exit Sub
End If

'More than 1 cell selected is a no no.
If Target.Cells.Count 1 Then
Exit Sub
End If

'Frank Kabel: Disabled as no longer needed
'If I already have a date then just format
'If IsDate(Target.Value) Then
' Target.NumberFormat = "dd-mmm-yyyy"
' Exit Sub
'End If

'Format as text to prevent dropping leading 0
Target.NumberFormat = "@"

'set the static variable
Set OldSelection = Target
End Sub



--
Regards
Frank Kabel
Frankfurt, Germany


Norman Harker wrote:
Hi Frank!

My final version is below.

I had trouble with the DateFormat constant. Also the TestRange
constant appears "sticky" so there might be change to that one.

I put back the format if the cell is already a date

I found a more acceptable response to amending a date which is to
amend the EndMacro error treatment: clear the bugger and format as
text. OK I still get the error message when I really shouldn't but it
doesn't then leave me with the date represented by the date serial
number of the entry.

Still a bit of testing to do, but I think it works OK. I'll post to
Chip. On his site, in the lead in he says, "If you use European style
dates (ddmmyyyy), you'll have to change some of the code." I'm going
to report him to the NSPCA!

I suppose that to be a bit more bullet proof it needs code that
checks
the date settings and then runs the US or European code accordingly.
But I think I'll put that in the very large "to do" file.