Custom Number Formatting

I'm creating a form where you can enter a credit card number. I set a custom number for the cell and described it as ####-####-####-####. You can enter a 16 digit number just fine, but when you tab away from the cell and the custom number takes place, it changes the last digit to a "0". Has anyone else encountered this? How do I fix it? I was told I could add an apostrophe before the entry, but this form is going to be used by hundreds of employees, so it needs to work automatically. Suggestions?

Excel keeps track of 15 significant digits. So as long as you enter the data as
a number, you're going to have trouble.

You just type in the text number:
1234-1234-1234-1234

But then you have to type the hyphens.

Or you could format the cell as Text (or start with a leading apostrophe) and a
helper column:

Type this:
'1234123412341234

and use this in that helper column:
=mid(a1,1,4)&"-"&mid(a1,5,4)&"-"&mid(a1,9,4)&"-"&mid(a1,13,4)

or you could use a worksheet event that does the work for you.

If you want to try this idea, rightclick on the worksheet tab that should have
this behavior. Select view code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTempVal As Variant

On Error GoTo errhandler:

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) = False Then Exit Sub

myTempVal = CDec(Target.Value)
Application.EnableEvents = False
Target.Value = Format(myTempVal, "0000-0000-0000-0000")

errhandler:
Application.EnableEvents = True

End Sub

I used all of column A in this line:
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
but you could use:
If Intersect(Target, Me.Range("c7:g99")) Is Nothing Then Exit Sub

But make sure whatever range you use is preformatted to text. If you leave it
general, then that 16th digit is already a 0 when the code starts.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

CSBUG wrote:
>
> I'm creating a form where you can enter a credit card number. I set a
> custom number for the cell and described it as ####-####-####-####. You
> can enter a 16 digit number just fine, but when you tab away from the
> cell and the custom number takes place, it changes the last digit to a
> "0". Has anyone else encountered this? How do I fix it? I was told I
> could add an apostrophe before the entry, but this form is going to be
> used by hundreds of employees, so it needs to work automatically.
> Suggestions?
>
> --
> CSBUG
> ------------------------------------------------------------------------
> CSBUG's Profile: http://www.excelforum.com/member.php...o&userid=28846
> View this thread: http://www.excelforum.com/showthread...hreadid=485994

--

Dave Peterson

Thanks for your help. For now I'm sticking with the text. It's a time issue. I'm anxious to test the other way you described.