Excel VBA form: force users to enter a letter follow by a number?

[VBA_Cancer]

Hi, let's say that I have a input box on my form called "searchbox". I want the users to have to enter a 6 digits postal code, ex. "L9A2C7". I want them to enter a letter follow by a number until they reach 6 digits - no space allow. So if they try to type mm for example, they would only have typed one m in there because you need to follow it up with a number first.

Is this possible?

Thanks

 

[pbornemeier]

Option Explicit

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Dim sText As String
    Dim lPos As Long
    Dim bOK As Boolean
    Dim sType As String

    sText = UserForm1.TextBox1.Text
    lPos = Len(sText) + 1

    Select Case KeyCode
    Case 49 To 57
        sType = "N"
    Case 65 To 90, 97 To 122
        sType = "A"
    Case Else
        bOK = True
        sType = "X"
    End Select
    
    Select Case lPos
    Case 1, 3, 5    'Alpha
        If sType = "A" Then bOK = True
    Case 2, 4, 6    'Number
        If sType = "N" Then bOK = True
    Case Else
        MsgBox "6-Character Max"
        KeyCode = 0
        bOK = True
    End Select
    
    If Not bOK Then
        MsgBox "Last character is invalid"
        KeyCode = 0
    End If
    
End Sub

 

[VBA_Cancer]

Thank you so much!, but this doesn't allow me to use backspace to edit them.

 

[VBA_Cancer]

I found a work around for this. I removed the case else and set the character limit to 6 on form initialize instead. But thank you!!

 

[MickG]

Another option , a bit late !!!
Try this:-
Could possibly be refined a bit more, but seems to do the trick !!!

Private [COLOR=navy]Sub[/COLOR] TextBox1_KeyPress(ByVal KeyAscii [COLOR=navy]As[/COLOR] MSForms.ReturnInteger)
[COLOR=navy]If[/COLOR] Not Chr(KeyAscii) Like "[a-zA-Z]" And TextBox1.Text = "" [COLOR=navy]Then[/COLOR]
KeyAscii = 0
[COLOR=navy]ElseIf[/COLOR] Len(TextBox1.Text) > 5 [COLOR=navy]Then[/COLOR]
    KeyAscii = 0
[COLOR=navy]ElseIf[/COLOR] Right(TextBox1.Text, 1) Like "[a-zA-Z]" [COLOR=navy]Then[/COLOR]
        [COLOR=navy]If[/COLOR] Not Chr(KeyAscii) Like "[0-9]" [COLOR=navy]Then[/COLOR] KeyAscii = 0
[COLOR=navy]ElseIf[/COLOR] Right(TextBox1.Text, 1) Like "[0-9]" [COLOR=navy]Then[/COLOR]
        [COLOR=navy]If[/COLOR] Not Chr(KeyAscii) Like "[a-zA-Z]" [COLOR=navy]Then[/COLOR] KeyAscii = 0
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[mumps]

I am assuming that you enter the postal code in a textbox called "TextBox1". Put a command button on your userform and place this macro in the command button code module. Open the userform, enter a postal code in the textbox and click the command button.

Private Sub CommandButton1_Click()
    Dim strReturn As String
    Dim RegEx As Object, Match As Object
    strReturn = TextBox1.Text
    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
        .MultiLine = False
        .Global = True
        .IgnoreCase = False
        .Pattern = "[A-Z][0-9][A-Z][0-9][A-Z][0-9]"
    End With
    
    Set Match = RegEx.Execute(strReturn)
    
    If Match.Count <> 1 Then
        MsgBox "Invalid postal code.  Please enter in this format: A5A6B6"
        TextBox1 = ""
        TextBox1.SetFocus
    Else
        Unload Me
    End If
End Sub

 

[sanj_edu]

Option Explicit

Private Sub CommandButton1_Click()
If TextBox1.BackColor = vbRed Then
Exit Sub
End If

If Len(TextBox1) <> 6 Then
TextBox1.BackColor = vbRed
Exit Sub

Else
Range("A1").Value = TextBox1.Value

End If

Unload Me


End Sub
Private Sub TextBox1_Change()
TextBox1.BackColor = vbWhite

End Sub
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case Len(Me.TextBox1.Text)
Case 0
If (KeyAscii > 64 And KeyAscii < 91) Or (KeyAscii > 96 And KeyAscii < 123) Or KeyAscii = 8 Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
MsgBox "Invalid key pressed"
TextBox1.BackColor = vbRed
TextBox1.SetFocus
End If
Case 1
If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 8 Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
MsgBox "Invalid key pressed"
TextBox1.BackColor = vbRed
TextBox1.SetFocus
End If
Case 2
If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 8 Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
MsgBox "Invalid key pressed"
TextBox1.BackColor = vbRed
TextBox1.SetFocus
End If
Case 3
If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 8 Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
MsgBox "Invalid key pressed"
TextBox1.BackColor = vbRed
TextBox1.SetFocus
End If
Case 4
If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 8 Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
MsgBox "Invalid key pressed"
TextBox1.BackColor = vbRed
TextBox1.SetFocus
End If
Case 5
If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 8 Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
MsgBox "Invalid key pressed"
TextBox1.BackColor = vbRed
TextBox1.SetFocus
End If
Case 6
KeyAscii = 0

End Select
End Sub

 

[pbornemeier]

A correction to my initial post:

Option Explicit

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    Dim sText As String
    Dim lPos As Long
    Dim bOK As Boolean
    Dim sPattern As String

    sText = UserForm1.TextBox1.Text
    sPattern = "ANANAN"
    lPos = Len(sText) + 1

    Select Case KeyAscii
    Case 48 To 57   '0-9
        If Mid(sPattern, lPos, 1) = "N" Then bOK = True
    Case 65 To 90   'A-Z
        If Mid(sPattern, lPos, 1) = "A" Then bOK = True
    Case 97 To 122  'a-z
        If Mid(sPattern, lPos, 1) = "A" Then bOK = True
        KeyAscii = KeyAscii - 32
    Case 8  'Backspace
        bOK = True
    Case 13
        If lPos = 1 Then
            KeyAscii = 0
            bOK = True
        End If
    Case Else
        bOK = False
    End Select
    
    If lPos > 6 Then
        MsgBox "6 character limit"
        KeyAscii = 0
    ElseIf Not bOK Then
        MsgBox "Entered character (" & Chr(KeyAscii) & ") is invalid and will not be retained."
        KeyAscii = 0
    End If
    
End Sub