I think I see what you mean. The cell will need to be formatted as text
before you key anything in as Excel will convert it right away. You could
ensure this by pasting this into your worksheet module (right click on sheet
tab and paste into the module)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect([Ccard], Target) Is Nothing Then _
Target.NumberFormat = "@"
End Sub

Then try some small changes to Pauls macro:

Sub FormatCcard()
If [Ccard].Formula Like "######" Then
[Ccard].Value = Format([Ccard].Value, "##xx xxxx xxxx ####")
ElseIf [Ccard].Formula Like "################" Then
[Ccard].Value = Format([Ccard].Value, "#### #### #### ####")
Else
MsgBox "Invalid Number"
End If

End Sub



"stefan via OfficeKB.com" wrote:


Hi JMB,
Thanks for the suggestion.
It stil lchanges the last digit.
This thing is driving me nuts. ;-)
Stefan

JMB wrote:
Copied the wrong line from stefans post. The "X" s/b "#"

Try:
[Ccard].Value = Application.Text([Ccard].Value, "#### #### #### ####")

: Hi,
: I have cell 'C25' or range CCard in which the user may enter the first 2
[quoted text clipped - 31 lines]

Paul D


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200507/1