On Mon, 17 Aug 2009 08:40:46 -0400, Ron Rosenfeld
wrote:

Dave,

The worksheet event routine you posted will still drop the 16th digit (turn it
into a zero).

I think if you did two events == first a selection change to make the
formatting "@" (text); then a worksheet change to test for an appropriate entry
and format it appropriately in VBA.

Something like this (without the tests for correct entry or error checking):
=======================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("a:a")) Is Nothing Then Exit Sub
If Target.Cells.Count < 1 Then Exit Sub
If Target.Cells.NumberFormat < "@" Then Exit Sub
Target.Value = Format(Target.Value, "0000-0000-0000-0000")
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Range("a:a")) Is Nothing Then Exit Sub
Target.NumberFormat = "@"
End Sub
==================================
--ron