Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ccard 6 or 16 digits
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ccard 6 or 16 digits
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ccard 6 or 16 digits
"stefan via OfficeKB.com" wrote in message
... : : 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ccard 6 or 16 digits
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ccard 6 or 16 digits
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ccard 6 or 16 digits
you could change
[Ccard].NumberFormat = "##xx xxxx xxxx ####" To [Ccard].Value = Application.Text([Ccard].Value, "##XX XXXX XXXX ####") "PaulD" wrote: "stefan via OfficeKB.com" wrote in message ... : : 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ccard 6 or 16 digits
Copied the wrong line from stefans post. The "X" s/b "#"
Try: [Ccard].Value = Application.Text([Ccard].Value, "#### #### #### ####") "PaulD" wrote: "stefan via OfficeKB.com" wrote in message ... : : 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ccard 6 or 16 digits
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
ccard 6 or 16 digits
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
ccard 6 or 16 digits
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
ccard 6 or 16 digits
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
ccard 6 or 16 digits
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you change single digits to recognized double digits? | Excel Worksheet Functions | |||
VBA write macro change column with 3 number digits to 4 digits the | Excel Discussion (Misc queries) | |||
number 12 digits to 15 digits | Excel Discussion (Misc queries) | |||
How to customize number to 10 digits including 2 digits after deci | Excel Worksheet Functions | |||
last ten digits | Excel Worksheet Functions |