Check if a textbox contains more than one letter

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,734
Office Version
2016
Platform
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

shknbk2

Board Regular
Joined
Mar 5, 2016
Messages
240
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
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,734
Office Version
2016
Platform
Windows
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

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.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,915
Office Version
2010
Platform
Windows
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.
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).
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,556
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:

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,734
Office Version
2016
Platform
Windows
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).
You are right about all you have raised. I should be able to insert numbers like you said.
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,734
Office Version
2016
Platform
Windows
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
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,915
Office Version
2010
Platform
Windows
You are right about all you have raised. I should be able to insert numbers like you 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
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,734
Office Version
2016
Platform
Windows
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
Exactly. I am very grateful
 

Watch MrExcel Video

Forum statistics

Threads
1,099,081
Messages
5,466,528
Members
406,484
Latest member
kaksolver

This Week's Hot Topics

Top