credit card formatting
 

I am trying to format a cell to a credit card format, so the user can enter
only the numbers and have the dashes between each set of four digits auto
populate. I have tried using both 0000-0000-0000-0000 and
####-####-####-####, and each of these puts a zero at the end. Any
suggestions? THanks in advance for your help.

PRE format column 3 click on sheet tabview codeinsert this.
Now when you type in a 16 digit number in col C below row 1 you will get it

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 2 Or Target.Column < 3 Then Exit Sub
Target.Value = Format(Target, "0000 0000 0000 0000")
End Sub

--
Don Guillett
SalesAid Software

"tifosi3" wrote in message
...
I am trying to format a cell to a credit card format, so the user can
enter
only the numbers and have the dashes between each set of four digits auto
populate. I have tried using both 0000-0000-0000-0000 and
####-####-####-####, and each of these puts a zero at the end. Any
suggestions? THanks in advance for your help.

You could use Validation. Assuming the data entry col. is
col. A, select col. A, go Data Validation,
choose "Custom", and in the "Formula" box put:

=AND(LEN($A1)=16,ISNUMBER(1*LEFT($A1,15)),ISNUMBER (1*RIGHT
($A1,15)))

But Validation is not bulletproof. A user can easily
override the Validation by copying/pasting or going
through the Edit Clear Clear All.

Format the column as "0000-0000-0000-0000".

HTH
Jason
Atlanta, GA


-----Original Message-----
I am trying to format a cell to a credit card format, so
the user can enter
only the numbers and have the dashes between each set of
four digits auto
populate. I have tried using both 0000-0000-0000-0000
and
####-####-####-####, and each of these puts a zero at
the end. Any
suggestions? THanks in advance for your help.
.

What is I am trying use a specific cell?

"Jason Morin" wrote:

You could use Validation. Assuming the data entry col. is
col. A, select col. A, go Data Validation,
choose "Custom", and in the "Formula" box put:

=AND(LEN($A1)=16,ISNUMBER(1*LEFT($A1,15)),ISNUMBER (1*RIGHT
($A1,15)))

But Validation is not bulletproof. A user can easily
override the Validation by copying/pasting or going
through the Edit Clear Clear All.

Format the column as "0000-0000-0000-0000".

HTH
Jason
Atlanta, GA


-----Original Message-----
I am trying to format a cell to a credit card format, so
the user can enter
only the numbers and have the dashes between each set of
four digits auto
populate. I have tried using both 0000-0000-0000-0000
and
####-####-####-####, and each of these puts a zero at
the end. Any
suggestions? THanks in advance for your help.
.

I tried this, did not seem to work, also I am looking for it to format to a
specific cell. Also I am not even basically proficient in VB, so I am having
trouble following the logic. Though I appreciate your help.

"Don Guillett" wrote:

PRE format column 3 click on sheet tabview codeinsert this.
Now when you type in a 16 digit number in col C below row 1 you will get it

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 2 Or Target.Column < 3 Then Exit Sub
Target.Value = Format(Target, "0000 0000 0000 0000")
End Sub

--
Don Guillett
SalesAid Software

"tifosi3" wrote in message
...
I am trying to format a cell to a credit card format, so the user can
enter
only the numbers and have the dashes between each set of four digits auto
populate. I have tried using both 0000-0000-0000-0000 and
####-####-####-####, and each of these puts a zero at the end. Any
suggestions? THanks in advance for your help.

Just PRE format the cell as TEXT and follow the instructions to insert the
macro changing the first line from

If Target.Row < 2 Or Target.Column < 3 Then Exit Sub
to
if target.address< "$A$2" then exit sub

Be SURE that the a is capitalized to A and that the " " are as shown.

--
Don Guillett
SalesAid Software

"tifosi3" wrote in message
...
I tried this, did not seem to work, also I am looking for it to format to
a
specific cell. Also I am not even basically proficient in VB, so I am
having
trouble following the logic. Though I appreciate your help.

"Don Guillett" wrote:

PRE format column 3 click on sheet tabview codeinsert this.
Now when you type in a 16 digit number in col C below row 1 you will get
it

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 2 Or Target.Column < 3 Then Exit Sub
Target.Value = Format(Target, "0000 0000 0000 0000")
End Sub

--
Don Guillett
SalesAid Software

"tifosi3" wrote in message
...
I am trying to format a cell to a credit card format, so the user can
enter
only the numbers and have the dashes between each set of four digits
auto
populate. I have tried using both 0000-0000-0000-0000 and
####-####-####-####, and each of these puts a zero at the end. Any
suggestions? THanks in advance for your help.

Select the cell and follow the steps I gave you. Change
$A1 in the formula to the cell you've chosen.

Jason

-----Original Message-----
What is I am trying use a specific cell?

"Jason Morin" wrote:

You could use Validation. Assuming the data entry col.
is
col. A, select col. A, go Data Validation,
choose "Custom", and in the "Formula" box put:

=AND(LEN($A1)=16,ISNUMBER(1*LEFT($A1,15)),ISNUMBER
(1*RIGHT
($A1,15)))

But Validation is not bulletproof. A user can easily
override the Validation by copying/pasting or going
through the Edit Clear Clear All.

Format the column as "0000-0000-0000-0000".

HTH
Jason
Atlanta, GA


-----Original Message-----
I am trying to format a cell to a credit card format,
so
the user can enter
only the numbers and have the dashes between each set
of
four digits auto
populate. I have tried using both 0000-0000-0000-
0000
and
####-####-####-####, and each of these puts a zero at
the end. Any
suggestions? THanks in advance for your help.
.


.