Check if a textbox contains more than one letter

kelly mort

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
These are a few things I want to bypass.

I am using textbox1 and textbox2

These are for textbox2:
1. The textbox should take only numbers and letters no other character accepted.

2. Letters should not start the entry. So A5 is not allowed

3. After a letter, no more input should be allowed. Eg if I type 12A and I press a valid character (numbers or letters), don't register it.

4. There are only 4 allowed letters: A B C D

5. If the letter is not the last character of textbox1, then don't register it.

That is if I have "ITEM-B" in textbox1 and I press say "A" in textbox2, don't register.

I hope someone can pull this up for me. It's hunting me so badly.

Thanks in advance
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
I think this satisfies all of your requirements. Paste it into the form code.
Code: 
Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Dim sTB2 As String, sKeyPress As String
    Dim lastLetterTB1 As String
    
    If TextBox1.Text <> "" Then
        lastLetterTB1 = UCase(Right(TextBox1.Text, 1))
    End If
    sKeyPress = Chr(KeyCode)
    If sKeyPress Like "[ABCD0123456789]" Then
        sTB2 = TextBox2.Text
        If sTB2 <> "" Then
            If Left(sTB2, 1) Like "[ABCD]" Or Right(sTB2, 1) Like "[ABCD]" Or _
              (sKeyPress Like "[ABCD]" And lastLetterTB1 <> sKeyPress) Then
                KeyCode = 0
            End If
        ElseIf KeyCode > 57 Then
            KeyCode = 0
        ElseIf sKeyPress Like "[ABCD]" And lastLetterTB1 <> sKeyPress Then
            KeyCode = 0
        End If
    ElseIf KeyCode >= 48 And KeyCode <> vbKeyDelete Then
        KeyCode = 0
    End If
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub
 
Upvote 0
shknbk2 said:
I think this satisfies all of your requirements. Paste it into the form code.
Code: 
Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Dim sTB2 As String, sKeyPress As String
    Dim lastLetterTB1 As String
    
    If TextBox1.Text <> "" Then
        lastLetterTB1 = UCase(Right(TextBox1.Text, 1))
    End If
    sKeyPress = Chr(KeyCode)
    If sKeyPress Like "[ABCD0123456789]" Then
        sTB2 = TextBox2.Text
        If sTB2 <> "" Then
            If Left(sTB2, 1) Like "[ABCD]" Or Right(sTB2, 1) Like "[ABCD]" Or _
              (sKeyPress Like "[ABCD]" And lastLetterTB1 <> sKeyPress) Then
                KeyCode = 0
            End If
        ElseIf KeyCode > 57 Then
            KeyCode = 0
        ElseIf sKeyPress Like "[ABCD]" And lastLetterTB1 <> sKeyPress Then
            KeyCode = 0
        End If
    ElseIf KeyCode >= 48 And KeyCode <> vbKeyDelete Then
        KeyCode = 0
    End If
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub
Click to expand...


Yes this does satisfy all the needs. I am very grateful.

One last thing:

How do I prevent the registering of the space key?

It seems to get registered when pressed. I don't want it to register.

Thanks again.
 
Upvote 0
kelly mort said:
3. After a letter, no more input should be allowed. Eg if I type 12A and I press a valid character (numbers or letters), don't register it.
Click to expand...
I have a question about what you meant for the above restriction. Does this mean once the letter has been typed, the user cannot go back and add (insert) any numbers before the letter (I'm thinking to add a number they forgot to type) nor select one or more numbers to edit them (I'm think typos here)? As written, the code shknbk2 posted blocks these actions (although it does permit the user to delete some of the previously typed numbers).
 
Upvote 0
I believe that this meets all conditions and it allows the user to insert a numeral in front of a terminal alpha chr.

Code: 
Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If TextBox1.Text <> vbNullString Then
        If UCase(Right(TextBox1.Text, 1)) <> UCase(Right(TextBox2.Text, 1)) Then
            If MsgBox("invalid last character", vbRetryCancel) = vbRetry Then
                Cancel = True
            Else
                TextBox2.Text = vbNullString
            End If
        End If
    End If
End Sub

Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyBack Then
        With TextBox2
            If .SelStart = 0 Or (.SelStart = 1 And .SelLength = 0) Then
                If Mid(.Text, .SelStart + .SelLength + 1) Like "[ABCD]" Then
                    KeyCode = 0
                    Beep
                End If
            End If
        End With
    End If
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Dim strChr As String
    Dim testString As String
    strChr = UCase(Chr(KeyAscii))
    KeyAscii = Asc(strChr)
    
    With TextBox2
        If .Text Like "*[ABCD]" Then
            If .SelStart = Len(.Text) Then
                testString = Chr(5)
            Else
                testString = "[0-9]"
            End If
        Else
            If .SelStart < Len(.Text) Then
                testString = "[0-9]"
            Else
                testString = "[0-9ABCD]"
            End If
        End If
    End With
    If strChr Like testString Then
    
    Else
        KeyAscii = 0
        Beep
    End If
End Sub
 
Last edited:
Upvote 0
Rick Rothstein said:
I have a question about what you meant for the above restriction. Does this mean once the letter has been typed, the user cannot go back and add (insert) any numbers before the letter (I'm thinking to add a number they forgot to type) nor select one or more numbers to edit them (I'm think typos here)? As written, the code shknbk2 posted blocks these actions (although it does permit the user to delete some of the previously typed numbers).
Click to expand...

You are right about all you have raised. I should be able to insert numbers like you said.
 
Upvote 0
mikerickson said:
I believe that this meets all conditions and it allows the user to insert a numeral in front of a terminal alpha chr.

Code: 
Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If TextBox1.Text <> vbNullString Then
        If UCase(Right(TextBox1.Text, 1)) <> UCase(Right(TextBox2.Text, 1)) Then
            If MsgBox("invalid last character", vbRetryCancel) = vbRetry Then
                Cancel = True
            Else
                TextBox2.Text = vbNullString
            End If
        End If
    End If
End Sub

Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyBack Then
        With TextBox2
            If .SelStart = 0 Or (.SelStart = 1 And .SelLength = 0) Then
                If Mid(.Text, .SelStart + .SelLength + 1) Like "[ABCD]" Then
                    KeyCode = 0
                    Beep
                End If
            End If
        End With
    End If
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Dim strChr As String
    Dim testString As String
    strChr = UCase(Chr(KeyAscii))
    KeyAscii = Asc(strChr)
    
    With TextBox2
        If .Text Like "*[ABCD]" Then
            If .SelStart = Len(.Text) Then
                testString = Chr(5)
            Else
                testString = "[0-9]"
            End If
        Else
            If .SelStart < Len(.Text) Then
                testString = "[0-9]"
            Else
                testString = "[0-9ABCD]"
            End If
        End If
    End With
    If strChr Like testString Then
    
    Else
        KeyAscii = 0
        Beep
    End If
End Sub
Click to expand...
Your code meets the conditions.

But I will wish to restrict registration of the key instead of registering it then show the alert with the before_update event.

What I did previously with the before_update event was to add the required letter automatically to the number in case I leave the box without adding it.

So now what I want is to make sure the wrong key does not register in the first place.

Thanks again.

Kelly
 
Upvote 0
kelly mort said:
You are right about all you have raised. I should be able to insert numbers like you said.
Click to expand...

See if this code works for you (note the Dim LastPosition As Long that is outside of any procedures... it is correctly located and must appear as shown).
Code: 
Dim LastPosition As Long

Private Sub TextBox2_Change()
  Static LastText As String
  Static SecondTime As Boolean
  If Not SecondTime Then
    With TextBox2
     If .Text Like "*[!0-9" & Right(TextBox1.Text, 1) & "]*" Or .Text Like "*[!0-9A-D]*" Or .Text Like "[!0-9]*" Or .Text Like "*#[A-D]?*" Then
        Beep
        SecondTime = True
        .Text = LastText
        .SelStart = LastPosition
      Else
        LastText = .Text
      End If
    End With
  End If
  SecondTime = False
End Sub

Private Sub TextBox2_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  LastPosition = TextBox2.SelStart
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  LastPosition = TextBox2.SelStart
End Sub
 
Upvote 0
Rick Rothstein said:
See if this code works for you (note the Dim LastPosition As Long that is outside of any procedures... it is correctly located and must appear as shown).
Code: 
Dim LastPosition As Long

Private Sub TextBox2_Change()
  Static LastText As String
  Static SecondTime As Boolean
  If Not SecondTime Then
    With TextBox2
     If .Text Like "*[!0-9" & Right(TextBox1.Text, 1) & "]*" Or .Text Like "*[!0-9A-D]*" Or .Text Like "[!0-9]*" Or .Text Like "*#[A-D]?*" Then
        Beep
        SecondTime = True
        .Text = LastText
        .SelStart = LastPosition
      Else
        LastText = .Text
      End If
    End With
  End If
  SecondTime = False
End Sub


Private Sub TextBox2_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  LastPosition = TextBox2.SelStart
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  LastPosition = TextBox2.SelStart
End Sub
Click to expand...

Exactly. I am very grateful
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Extracting data with VBA macro button from files in designated folder
2 3
Replies
20
Views
1K
Micron
M
N
  • Solved
Respective Digits in Respective numbered Textbox
Replies
8
Views
294
NimishK
N
D
  • Question Question
How to keep a tally of matching letters in separate cells
Replies
4
Views
666
Davenil
D
D
  • Question Question
Modify a worksheet without registering it as a change
Replies
1
Views
128
Kaper
Kaper
R
  • Question Question
Letters Log Due Date Reminder
Replies
2
Views
373
Rayreloj10
R

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

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