VBA how do I get 2 textboxes to display different strings based on keypress?

yytsunamiyy

yytsunamiyy

Well-known Member
Joined
Mar 17, 2008
Messages
963
Hi all,

after a lot of silent reading and marvelling at your wisdom as a guest I decided to register und molest you with my problem.. :smile:

I have a Userform whose purpose it is to capture payment information, in particular Creditcard information. To this end it has (amongst other elements) 2 textboxes - textboxCardNr1 and TextboxCardnr2.

the User enters the cardnr in Textboxcardnr1 as, e.g. 4321098765432109. Through the code below I transform this into the more readable number
4321-0987-6543-2109. I capture the keypress instance to only allow numbers to be entered in this field and to simultaniously update TextboxCardNr2 with the keystrokes.

On exiting TextboxCardNr1 the content of TextboxCardNr2 is copied to the clipboard to be available for pasting on a secure website in the approriate field.

My problem is, that when the user makes a mistake and changes the number in TextboxCardNr1, those changes are not automatically mirrored in TextboxCardNr2.

I want the easy to read form of the card - with the dashes, to be saved. The website the user is pasting the content of TextboxCardnr2 to does not accept the number with dashes.

Any help on how to ensure that correct creditcardnumber is available without dashes for pasting but shown with dashes for easier reading would be greatly appreciated.
Also, if anybody has code or any other ideas on how to check the validity of a card - I guess its some kind of checksum in the numbers - a tip would be highly appreciated.

The code for TextboxCardNr1 follows (~Nr2 does not have code as the user never touches it):


Code: 
 Private Sub TextBoxCardNr1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim taste As String
Dim wert As String

taste = VBA.Chr(KeyAscii)
wert = Me.TextBoxCardNr1.Text

KeyAscii = 0
If taste = "0" Or taste = "1" Or taste = "2" Or taste = "3" Or taste = "4" Or taste = "5" Or taste = "6" Or taste = "7" Or taste = "8" Or taste = "9" Then[INDENT]     If Not Me.TextBoxCardNr2.Text Like "################" Then
        Me.TextBoxCardNr2.Text = Me.TextBoxCardNr2.Text & taste
    End If
[/INDENT][INDENT]     If wert = "" Or wert Like "#" Or wert Like "##" Or wert Like "###" Then[INDENT]         wert = wert & taste
[/INDENT]ElseIf wert Like "####" Then[INDENT]         wert = wert & "-" & taste
[/INDENT]ElseIf wert Like "####-" Or wert Like "####-#" Or wert Like "####-##" Or wert Like "####-###" Then[INDENT]         wert = wert & taste
[/INDENT]ElseIf wert Like "####-####" Then[INDENT]         wert = wert & "-" & taste
[/INDENT]ElseIf wert Like "####-####-" Or wert Like "####-####-#" Or wert Like "####-####-##" Or wert Like "####-####-###" Then[INDENT]         wert = wert & taste
[/INDENT]ElseIf wert Like "####-####-####" Then[INDENT]         wert = wert & "-" & taste
[/INDENT]ElseIf wert Like "####-####-####-" Or wert Like "####-####-####-#" Or wert Like "####-####-####-##" Or wert Like "####-####-####-###" Then[INDENT]         wert = wert & taste
[/INDENT]End If

    Me.TextBoxCardNr1.Text = wert
[/INDENT]Else[INDENT]Me.TextBoxCardNr1.Text = wert
[/INDENT]End If

End Sub

Private Sub TextBoxCardNr1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Me.ComboBoxCardType.Text = "VS" And TextBoxCardNr1.Text Like "4###-####-####-####" Then[INDENT]Me.TextBoxCardNr2.SelStart = 0
             Me.TextBoxCardNr2.SelLength = 50
TextBoxCardNr2.Copy
             Me.TextBoxCardSecCode.SetFocus
[/INDENT]ElseIf Me.ComboBoxCardType.Text = "MA" And TextBoxCardNr1.Text Like "5###-####-####-####" Then[INDENT]             Me.TextBoxCardNr2.SelStart = 0
             Me.TextBoxCardNr2.SelLength = 50
             TextBoxCardNr2.Copy
             Me.TextBoxCardSecCode.SetFocus
[/INDENT]ElseIf Me.ComboBoxCardType.Text = "AE" And TextBoxCardNr1.Text Like "3###-####-####-####" Then[INDENT]             Me.TextBoxCardNr2.SelStart = 0
             Me.TextBoxCardNr2.SelLength = 50
             TextBoxCardNr2.Copy
             Me.TextBoxCardSecCode.SetFocus
[/INDENT]ElseIf Me.ComboBoxCardType.Text = "DI" And TextBoxCardNr1.Text Like "3###-####-####-####" Then[INDENT]             Me.TextBoxCardNr2.SelStart = 0
             Me.TextBoxCardNr2.SelLength = 50
             TextBoxCardNr2.Copy
             Me.TextBoxCardSecCode.SetFocus
[/INDENT]ElseIf Not TextBoxCardNr1.Text = "" Then[INDENT]     MsgBox "Esta tarjeta no puede ser de tipo " & Me.ComboBoxCardType.Text & "." & vbCr & vbCr & _
            "Por favor examinar tipo y numero." & vbCr & vbCr & _
            "Numeros de tarjetas empiezan con" & vbCr & _
            "3 son de Diners Club o de AmEx," & vbCr & _
            "4 son de Visa," & vbCr & _
            "5 son de Mastercard.", vbOKOnly Or vbExclamation
     Me.TextBoxCardNr2.Text = ""
     Me.TextBoxCardNr1.Text = ""
     Me.ComboBoxCardType.SetFocus
[/INDENT]End If
            

End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Could you just use something as simple as this to get the value into the second text box and then use the same code you have to copy it?

Private Sub TextBox1_Change()
Dim First As String
First = TextBox1.Value
If Len(First) = 16 Then
TextBox2.Value = Left(First, 4) & "-" & Mid(First, 5, 4) & "-" & Mid_
(First, 9, 4) & "-" & Right(First, 4)
Else
TextBox2.Value = ""
End If
End Sub

Adam
 
Upvote 0
Thanks Adam, your tip helped me a lot. after some thinking along your lines I came up with the following:

Code: 
Private Sub TextBoxCardNr1_Change()
Dim wert As String
wert = Me.TextBoxCardNr1.Text

If Len(wert) = 5 Then
    wert = VBA.Left(wert, 4) & "-" & VBA.Right(wert, 1)
    
ElseIf Len(wert) = 10 Then
    wert = VBA.Left(wert, 4) & "-" & VBA.Mid(wert, 6, 4) & "-" & VBA.Right(wert, 1)
    
ElseIf Len(wert) = 15 Then
    
        wert = VBA.Left(wert, 4) & "-" & VBA.Mid(wert, 6, 4) & "-" & VBA.Mid(wert, 11, 4) & "-" & VBA.Right(wert, 1)
    
End If
Me.TextBoxCardNr1.Text = wert
End Sub

Private Sub TextBoxCardNr1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim wert As String
wert = TextBoxCardNr1.Text

If Me.ComboBoxCardType.Text = "VS" And wert Like "4###-####-####-####" Then
            GoTo updateNr2
ElseIf Me.ComboBoxCardType.Text = "MA" And TextBoxCardNr1.Text Like "5###-####-####-####" Then
            GoTo updateNr2
ElseIf Me.ComboBoxCardType.Text = "AE" And TextBoxCardNr1.Text Like "3###-####-####-####" Then
            GoTo updateNr2
ElseIf Me.ComboBoxCardType.Text = "DI" And TextBoxCardNr1.Text Like "3###-####-####-####" Then
            GoTo updateNr2
ElseIf Not TextBoxCardNr1.Text = "" Then
    MsgBox "Esta tarjeta no puede ser de tipo " & Me.ComboBoxCardType.Text & "." & vbCr & vbCr & _
           "Por favor examinar tipo y numero." & vbCr & vbCr & _
           "Numeros de tarjetas empiezan con" & vbCr & _
           "3 son de Diners Club o de AmEx," & vbCr & _
           "4 son de Visa," & vbCr & _
           "5 son de Mastercard.", vbOKOnly Or vbExclamation
    Me.TextBoxCardNr2.Text = ""
    Me.TextBoxCardNr1.Text = ""
    Me.ComboBoxCardType.SetFocus
    Exit Sub
End If
          
updateNr2:
    Me.TextBoxCardNr2.Value = VBA.Left(wert, 4) & VBA.Mid(wert, 6, 4) & VBA.Mid(wert, 11, 4) & VBA.Right(wert, 4)
    Me.TextBoxCardNr2.SelStart = 0
    Me.TextBoxCardNr2.SelLength = 16
    TextBoxCardNr2.Copy
    Me.TextBoxCardSecCode.SetFocus

End Sub

Private Sub TextBoxCardNr1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim taste As String
Dim nummer As Integer

taste = VBA.Chr(KeyAscii)

On Error GoTo 2

nummer = taste
Exit Sub

    
2    KeyAscii = 0
    



End Sub
It is now behaving as I would like it too. Thank you again.

Stephan
 
Last edited:
Upvote 0
yytsunamiyy

Glad you've got this working.:)

But that seems like a lot of code for something that appears so simple.

Why not use the textbox exit event to check the data?
 
Upvote 0
Hi Norie, thats what i'm doing :)

with the following steps:

1) keypress event ensures only numbers are entered in the
TextboxCardNr1

2) Change event updates the number entered in TextboxCardNr1 with dashes while typing

3) exit event checks that Cardtype selected in ComboboxCardtype matches with first number entered (here i would like a better validity check of the entered cardnumber, see also here) and writes the information less the dashes in TextboxCardNr2 for further use
 
Upvote 0
My bad.:oops:

I think I just got a little overwhelmed by all the code and went temporarily short-sighted.:)

Mind you I would still suggest only using the Exit event.

And I'm also having trouble following the code due to the way you've structured it.
 
Upvote 0
hm... - If I only use the exit event I can't controll that only numbers are entered - that's the job of the keypress event,, while the change event just updates the typed data with dashes - just to make it easier to read. everyving else IS handled by the exit event. Maybe I'm just a bit slow today, but I fail to see how I would be able to get all of this functionality into ONLY the exit event.

Please excuse that the code is not too well structured - I'm pretty new to vba and my programming knowledge stems from the times when datasettes and basic were all the rage - havent done any coding since then...
I am sure that a lot of the code I am currently writing could be much tighter and tidier - but after all we live and learn and as long as it works as I want it to I dont worry too much about having a few unnessecary lines and some structuring issues.
thank you again for your help. :)
 
Upvote 0
yytsunamiyy said:
hm... - If I only use the exit event I can't controll that only numbers are entered
Click to expand...
Sorry but that's just not true.:)

Sure the user could enter non-numeric values but you could easily check after the matter if they have.

And if you notice there's a parameter in the Exit event - Cancel.

Setting that to True in the code means the user wouldn't leave without entering a valid value.
 
Upvote 0
Seeing that the keypress event now handles the checking in what is essentially 2 lines I think the point is moot. I just prefer the User notr be be able to press the wrong key from the beginning, rather than stopping him later - I guess it's just a matter of preference.

Code: 
Private Sub TextBoxCardNr1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Dim taste As String  'taste is german and means key :)
Dim nummer As Integer

taste = VBA.Chr(KeyAscii) 'transform keypress to Charactervalue (String)

On Error GoTo 2

nummer = taste  'transform string to integer - on error change keyAscii Value to 0 
Exit Sub
    
2    KeyAscii = 0
End Sub
 
Upvote 0
The fact that you are using Excel, a application without adequate security measures, to store people's credit card information is scary. You might want to rethink this decision.
 
Upvote 0
You must log in or register to reply here.

Similar threads

yytsunamiyy
  • Question Question
automated textbox updating in Userform, Creditcard Checksum
Replies
2
Views
939
yytsunamiyy
yytsunamiyy

Forum statistics

Threads
1,226,527
Messages
6,191,574
Members
453,665
Latest member
WaterWorks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top