CODEWITHSUNDEEP
vbainputtextboxcontrols
Rob Robinson
Rob Robinson

Reputation: 1

How to force the input of a textbox on a userform to be a specific format in VBA

I have a user form with a series of input textboxes and am looking for a way to prevent the user from inputing anything other than 5 digits followed by one letter followed by 4 digits. E.g. 12345A6789

I can see a way to use only numeric and only alphabetic and create three text boxes and then somehow merge them all together- although not sure how to do that either. Ideally there would just be one textbox.

Any help is appreciated

Upvotes: 0

Views: 3099

Answers (2)

Rob Robinson
Rob Robinson

Reputation: 1

I came up with a solution that works okay, I'm sure this could be improved to prevent the user inputting special characters but this works well enough to reduce most errors

'Checks that document number is the correct format (5 digits, 1 letter, 4 digits)
Sub TextBox_DocumentNumber_AfterUpdate()

If (IsNumeric(Left(TextBox_DocumentNumber, 5))) = False Then
Call DocumentNumberFormat


ElseIf (IsNumeric(Mid(TextBox_DocumentNumber, 7))) = True Then
Call DocumentNumberFormat


ElseIf (IsNumeric(Right(TextBox_DocumentNumber, 4))) = False Then
Call DocumentNumberFormat

End If

End Sub



Sub DocumentNumberFormat()

Dim DocNum As String
Dim Response As VbMsgBoxResult

DocNum = MsgBox("Please enter document numbers in the formart 00000-A-0000", vbRetryCancel + vbExclamation, "Incorrect Format")


UserForm3.TextBox_DocumentNumber = ""
If DocNum = vbCancel Then
           Call Cancel
End If

Exit Sub
End Sub





'Auto inserts hyphens and sets max length for document number
Private Sub TextBox_DocumentNumber_Change()
If Len(TextBox_DocumentNumber) = 5 Then TextBox_DocumentNumber = TextBox_DocumentNumber & "-"
If Len(TextBox_DocumentNumber) = 7 Then TextBox_DocumentNumber = TextBox_DocumentNumber & "-"
TextBox_DocumentNumber.MaxLength = 12
End Sub

Upvotes: 0

Comintern
Comintern

Reputation: 22205

The built in Textbox is a little lacking in this department. The way I'd approach this would be to use the Change event to enforce formatting. Note that the Key* events won't handle any of the other ways to get text into a Textbox (drag-drop, copy-paste, etc.). Something along these lines (this is for zip + 4, but a similar concept would work):

Private Sub TextBox1_Change()
    Static reentry As Boolean                           'anti-recursion flag
    If reentry Then Exit Sub
    Dim chars() As Byte
    chars = StrConv(TextBox1.Text, vbFromUnicode)

    Dim buffer As String
    Dim i As Integer
    For i = LBound(chars) To UBound(chars)
        If Len(buffer) = 5 Then buffer = buffer & "-"   'auto-insert the dash
        If Len(buffer) = 10 Then Exit For               'limit to 10 chars
        If chars(i) >= 48 And chars(i) <= 57 Then       'ignore anything but numbers.
            buffer = buffer & Chr$(chars(i))
        End If
    Next i

    reentry = True
    TextBox1.Text = buffer
    reentry = False
End Sub

Upvotes: 1

Related Questions