ccard 6 or 16 digits

[stefan via OfficeKB.com]

Hi,
I have cell 'C25' or range CCard in which the user may enter the first 2 and
lst 4 digits of a credit card or a full 16 digit card number.
Apparently it seems to be the best way to avoid excels "15-digit rule" to to
a macro in a worksheet change module.
What i'd like to accomplish is that if the user inputs the 2/4 numbers the
result is 54xx xxxx xxxx 3210 and if the user inputs the full number 16
digits it results in 5411 1111 1111 3210. I cant figure out how i can
incorporate this in my existing worksheet change event which is at the end of
this post.
Thank you for your help.

Stefan

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If bDisableEvents Then Exit Sub
If Intersect(Target, Me.Range("C28:H28")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
bDisableEvents = True
With Me
.Range("28:28").Insert
.Range("29:29").Copy Range("28:28")
.Range("28:28").ClearContents
.Range("D29").Select
End With
bDisableEvents = False
End Sub


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

[Jim Cone]

Stefan,

The following uses the selection change event, maybe it
can get you started...
Jim Cone
San Francisco, USA

'--------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strCardNum As String
Dim strCaption As String

strCaption = "Process Card Number"
TryAgain:
If Target.Address = "$C$25" Then
strCardNum = VBA.InputBox("Enter the card number as..." & vbCr & _
"First 2 digits and last 4 digits: 12 3456" & vbCr & _
"or" & vbCr & _
"All 16 digits: 1234 5678 9101 1121", strCaption)
End If

If Len(strCardNum) = 0 Then
Exit Sub
ElseIf Len(strCardNum) = 7 Then
'Verify number is valid then
Range("C25").Value = strCardNum
ElseIf Len(strCardNum) = 19 Then
'Verify number is valid then
Range("C25").Value = strCardNum
Else
strCaption = "Please try again"
GoTo TryAgain
End If
End Sub
'---------------------------


"stefan via OfficeKB.com" <forum@OfficeKB.com>
wrote in message news:51611226301E2@OfficeKB.com...

Hi,
I have cell 'C25' or range CCard in which the user may enter the first 2 and
lst 4 digits of a credit card or a full 16 digit card number.
Apparently it seems to be the best way to avoid excels "15-digit rule" to to
a macro in a worksheet change module.
What i'd like to accomplish is that if the user inputs the 2/4 numbers the
result is 54xx xxxx xxxx 3210 and if the user inputs the full number 16
digits it results in 5411 1111 1111 3210. I cant figure out how i can
incorporate this in my existing worksheet change event which is at the end of
this post.
Thank you for your help.
Stefan

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If bDisableEvents Then Exit Sub
If Intersect(Target, Me.Range("C28:H28")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
bDisableEvents = True
With Me
.Range("28:28").Insert
.Range("29:29").Copy Range("28:28")
.Range("28:28").ClearContents
.Range("D29").Select
End With
bDisableEvents = False
End Sub

[PaulD]

"stefan via OfficeKB.com" <forum@OfficeKB.com> wrote in message
news:51611226301E2@OfficeKB.com...
:
: Hi,
: I have cell 'C25' or range CCard in which the user may enter the first 2
and
: lst 4 digits of a credit card or a full 16 digit card number.
: Apparently it seems to be the best way to avoid excels "15-digit rule" to
to
: a macro in a worksheet change module.
: What i'd like to accomplish is that if the user inputs the 2/4 numbers the
: result is 54xx xxxx xxxx 3210 and if the user inputs the full number 16
: digits it results in 5411 1111 1111 3210. I cant figure out how i can
: incorporate this in my existing worksheet change event which is at the end
of
: this post.
: Thank you for your help.
:
: Stefan

This will do a basic check of the range CCard and format the cell
accordingly

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

Paul D

[stefan via OfficeKB.com]

Hi Paul,
Thank you for your quick answer.
I believe that the line = [Ccard].NumberFormat = "#### #### #### ####"
brings me back to the problem that excel has with numbers largen then 15
digits. If the string could become a textformat at this point i believe it
would take care of the problem. Do you know how this formating can be changed?

I guess i should have used a better example. If the user inputs
5555444433332222, excel converts it to 5555444433332220.
Thanks.
Stefan

PaulD wrote:
>: Hi,
>: I have cell 'C25' or range CCard in which the user may enter the first 2 and
>[quoted text clipped - 9 lines]
>:
>: Stefan
>
>This will do a basic check of the range CCard and format the cell
>accordingly
>
>Sub FormatCcard()
> If IsNumeric([Ccard].Value) Then
> If [Ccard].Formula Like "######" Then
> [Ccard].NumberFormat = "##xx xxxx xxxx ####"
> ElseIf [Ccard].Formula Like "################" Then
> [Ccard].NumberFormat = "#### #### #### ####"
> Else
> MsgBox "Invalid Number"
> End If
> Else
> MsgBox "Not a Number"
> End If
>End Sub
>
>Paul D


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

[stefan via OfficeKB.com]

Hi Jim,
Thank you for the code. I'll have to take closer look at it. I dont want the
user use a input box, but enter directly into the cell. So i'll poke around
with it.
I appreciate your quick reply.
Stefan

Jim Cone wrote:
>Stefan,
>
>The following uses the selection change event, maybe it
>can get you started...
>Jim Cone
>San Francisco, USA
>
>'--------------------------
>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>Dim strCardNum As String
>Dim strCaption As String
>
>strCaption = "Process Card Number"
>TryAgain:
>If Target.Address = "$C$25" Then
> strCardNum = VBA.InputBox("Enter the card number as..." & vbCr & _
> "First 2 digits and last 4 digits: 12 3456" & vbCr & _
> "or" & vbCr & _
> "All 16 digits: 1234 5678 9101 1121", strCaption)
> End If
>
> If Len(strCardNum) = 0 Then
> Exit Sub
> ElseIf Len(strCardNum) = 7 Then
> 'Verify number is valid then
> Range("C25").Value = strCardNum
> ElseIf Len(strCardNum) = 19 Then
> 'Verify number is valid then
> Range("C25").Value = strCardNum
> Else
> strCaption = "Please try again"
> GoTo TryAgain
> End If
>End Sub
>'---------------------------
>
>Hi,
>I have cell 'C25' or range CCard in which the user may enter the first 2 and
>lst 4 digits of a credit card or a full 16 digit card number.
>Apparently it seems to be the best way to avoid excels "15-digit rule" to to
>a macro in a worksheet change module.
>What i'd like to accomplish is that if the user inputs the 2/4 numbers the
>result is 54xx xxxx xxxx 3210 and if the user inputs the full number 16
>digits it results in 5411 1111 1111 3210. I cant figure out how i can
>incorporate this in my existing worksheet change event which is at the end of
>this post.
>Thank you for your help.
>Stefan
>
>Private Sub Worksheet_Change(ByVal Target As Range)
>On Error Resume Next
>If bDisableEvents Then Exit Sub
>If Intersect(Target, Me.Range("C28:H28")) Is Nothing Then Exit Sub
>If Target = "" Then Exit Sub
>bDisableEvents = True
> With Me
> .Range("28:28").Insert
> .Range("29:29").Copy Range("28:28")
> .Range("28:28").ClearContents
> .Range("D29").Select
> End With
>bDisableEvents = False
>End Sub


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

[JMB]

you could change

[Ccard].NumberFormat = "##xx xxxx xxxx ####"

To
[Ccard].Value = Application.Text([Ccard].Value, "##XX XXXX XXXX ####")

"PaulD" wrote:

> "stefan via OfficeKB.com" <forum@OfficeKB.com> wrote in message
> news:51611226301E2@OfficeKB.com...
> :
> : Hi,
> : I have cell 'C25' or range CCard in which the user may enter the first 2
> and
> : lst 4 digits of a credit card or a full 16 digit card number.
> : Apparently it seems to be the best way to avoid excels "15-digit rule" to
> to
> : a macro in a worksheet change module.
> : What i'd like to accomplish is that if the user inputs the 2/4 numbers the
> : result is 54xx xxxx xxxx 3210 and if the user inputs the full number 16
> : digits it results in 5411 1111 1111 3210. I cant figure out how i can
> : incorporate this in my existing worksheet change event which is at the end
> of
> : this post.
> : Thank you for your help.
> :
> : Stefan
>
> This will do a basic check of the range CCard and format the cell
> accordingly
>
> Sub FormatCcard()
> If IsNumeric([Ccard].Value) Then
> If [Ccard].Formula Like "######" Then
> [Ccard].NumberFormat = "##xx xxxx xxxx ####"
> ElseIf [Ccard].Formula Like "################" Then
> [Ccard].NumberFormat = "#### #### #### ####"
> Else
> MsgBox "Invalid Number"
> End If
> Else
> MsgBox "Not a Number"
> End If
> End Sub
>
> Paul D
>
>
>

[JMB]

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

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

"PaulD" wrote:

> "stefan via OfficeKB.com" <forum@OfficeKB.com> wrote in message
> news:51611226301E2@OfficeKB.com...
> :
> : Hi,
> : I have cell 'C25' or range CCard in which the user may enter the first 2
> and
> : lst 4 digits of a credit card or a full 16 digit card number.
> : Apparently it seems to be the best way to avoid excels "15-digit rule" to
> to
> : a macro in a worksheet change module.
> : What i'd like to accomplish is that if the user inputs the 2/4 numbers the
> : result is 54xx xxxx xxxx 3210 and if the user inputs the full number 16
> : digits it results in 5411 1111 1111 3210. I cant figure out how i can
> : incorporate this in my existing worksheet change event which is at the end
> of
> : this post.
> : Thank you for your help.
> :
> : Stefan
>
> This will do a basic check of the range CCard and format the cell
> accordingly
>
> Sub FormatCcard()
> If IsNumeric([Ccard].Value) Then
> If [Ccard].Formula Like "######" Then
> [Ccard].NumberFormat = "##xx xxxx xxxx ####"
> ElseIf [Ccard].Formula Like "################" Then
> [Ccard].NumberFormat = "#### #### #### ####"
> Else
> MsgBox "Invalid Number"
> End If
> Else
> MsgBox "Not a Number"
> End If
> End Sub
>
> Paul D
>
>
>

[stefan via OfficeKB.com]

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

[Dave Peterson]

If you don't enter the value as text (leading apostrophe or preformatting the
cell as text), then the value is truncated to 15 significant digits (plus that
0) before any code can run.



"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

--

Dave Peterson

[JMB]

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
>

[stefan via OfficeKB.com]

Hi JMB,
I like that. I applied this to my worksheet_change vs. the
Worksheet_SelectionChange event.
i could not figure out how to incorporate it into the
Worksheet_SelectionChange and function properly. (all my columns of the
worksheet expanded to full screen or larger - strange.) when i add it as
shown below to worksheet change it works, as far as i can tell, without any
problems. i am happy with that. thank you for the help.

i was wondering if there might be an option to add a msg - if the value
entered in the range 'ccard' is less then 6 digits, more then 16 or between 6
and 16 digits. when i leave the code
> Else
> MsgBox "Invalid Number"
in, it prompts at any worksheet_change (obviously).

Thank you
Stefan

....
If Not Intersect([Ccard], Target) Is Nothing Then _
Target.NumberFormat = "@"
If [Ccard].Formula Like "######" Then
[Ccard].Value = Format([Ccard].Value, "##xx xxxx xxxx ####")
ElseIf [Ccard].Formula Like "################" Then
[Ccard].Value = Format([Ccard].Value, "#### #### #### ####")
End If
....



JMB wrote:
>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
>
>> Hi JMB,
>> Thanks for the suggestion.
>[quoted text clipped - 12 lines]
>> >>
>> >> Paul D


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

[JMB]

you could change the else statement to test the length for 6 or 16 if you
want to know the length of what they input.

If (Len(Target.Value) <> 6) And (Len(Target.Value) <> 16) Then _
MsgBox "Invalid Length"

Encapsulate the entire pattern matching test with the first If statement as
follows and you won't get the message box for every change event.

If Not Intersect([Ccard], Target) Is Nothing Then
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 Data. Please enter a 6 or 16 digit number."
End If
End If

As long as you preformat the cell to text (Format/Cells/Text), you wouldn't
have to change the numberformat to text via code. You could not rely on the
Change event to set the format to text as it would be too late.


"stefan via OfficeKB.com" wrote:

>
> Hi JMB,
> I like that. I applied this to my worksheet_change vs. the
> Worksheet_SelectionChange event.
> i could not figure out how to incorporate it into the
> Worksheet_SelectionChange and function properly. (all my columns of the
> worksheet expanded to full screen or larger - strange.) when i add it as
> shown below to worksheet change it works, as far as i can tell, without any
> problems. i am happy with that. thank you for the help.
>
> i was wondering if there might be an option to add a msg - if the value
> entered in the range 'ccard' is less then 6 digits, more then 16 or between 6
> and 16 digits. when i leave the code
> > Else
> > MsgBox "Invalid Number"
> in, it prompts at any worksheet_change (obviously).
>
> Thank you
> Stefan
>
> ....
> If Not Intersect([Ccard], Target) Is Nothing Then _
> Target.NumberFormat = "@"
> If [Ccard].Formula Like "######" Then
> [Ccard].Value = Format([Ccard].Value, "##xx xxxx xxxx ####")
> ElseIf [Ccard].Formula Like "################" Then
> [Ccard].Value = Format([Ccard].Value, "#### #### #### ####")
> End If
> ....
>
>
>
> JMB wrote:
> >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
> >
> >> Hi JMB,
> >> Thanks for the suggestion.
> >[quoted text clipped - 12 lines]
> >> >>
> >> >> Paul D
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200507/1
>