Solved: Force characters in text box

[blackie42]

Hi,

In a userform 'textbox' is there a way to force the user to input a fixed number of characters (either numbers or letters or a mix) e.g. 9 characters. I know there is a max field length in properties.

thanks

jon

[Oorang]

Unfortunatly there is no Minimum Length property (would be nice though:)). You can build it yourself though:
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Const lngMinLen_c As Long = 9
Dim lngFldLen As Long
lngFldLen = VBA.Len(Me.TextBox1.Value)
If lngFldLen Then 'Allows empty fields.
If lngFldLen < lngMinLen_c Then
Cancel = True
VBA.MsgBox "The value in this field must be at least 9 characters.", _
vbInformation Or vbSystemModal, "Tip:"
End If
End If
End Sub

[blackie42]

Thanks or your speedy response

One more Q - is it possible (within this same code perhaps?) to automatically capitalize any 'letter' characters entered?

thanks again

[Oorang]

Private Sub TextBox1_Keyup(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Me.TextBox1.Value = UCase$(Me.TextBox1.Value)
End Sub

[blackie42]

excellent - both work fine as I expected they would.

One more on text boxes is it posible to force user to input 'number' characters for first seven, followed by a 'letter' then a final number?

thanks a lot

Jon

[Oorang]

Yes but it will start to more code-intensive on you. You use the KeyDown event to intercept the keystroke. The event parameter "keycode" will tell you what key was pushed. If it is not a key you want, you set it to zero: cancelling the input. This is all very simple sounding but there are a few thing to remember... The key down event catches every keystroke, backspace, delete, etc. So you want to be very carfull about what you do or not set to null. As luck would have it, I already have a fairly decent procedure built for an application that will catch non-numerics without disabling other functions. It should be enough to get you started on your routine to disable non-alphas.
Option Explicit

Private Sub TextBox1_KeyDown(ByVal keyCode As MSForms.ReturnInteger, _
ByVal shift As Integer)
Const lngNullChar_c As Long = 0
Select Case VBA.Len(Me.TextBox1.Value)
Case 0 To 6
keyCode = VettedNumeric(CInt(keyCode), shift)
Case 7
'You will probabally want to put something
'more sophisticated here:
If keyCode < vbKeyA Then
keyCode = lngNullChar_c
ElseIf keyCode > vbKeyZ Then
keyCode = lngNullChar_c
End If
Case Else
'Further input will cause user to be advanced to next field.
keyCode = vbKeyTab
End Select
End Sub

Private Function VettedNumeric(keyCode As Integer, shift As Integer, Optional _
silent As Boolean) As Long
'--------------------------------------------------------------------------
' Procedure : VettedNumeric
' Author : Aaron Bush
' Date : 10/09/2007
' Purpose : Works in conjuction with a keydown event. Will return keycode
' if it is numeric, a decimal point, or a non-text input.
' Input(s) : keyCode - The keycode parameter from a KeyDown event.
' shift - The shift paramater from a KeyDown event.
' silent - If True, function will not beep when an illegal
' key is pressed.
' Output : The keycode that was input, or the 0 (null char).
' Remarks : Works by eliminating any input from keyboard that will
' generate text data. All key combinations with ctrl or alt are
' allowed, all function keys are allowed, all numerics and the
' decimal point are allowed.
'--------------------------------------------------------------------------

Const lngKeySemiColn_c As Long = 186
Const lngKeyHypnUscr_c As Long = 189
Const lngKeyFwrdSlsh_c As Long = 191
Const lngKeyTldaGrve_c As Long = 192
Const lngLeftBracket_c As Long = 219
Const lngRghtBracket_c As Long = 221
Const lngNullChrcter_c As Long = 0
Const lngPrdGrtrThan_c As Long = 190
Const acShiftMask As Long = 1
On Error GoTo Err_Hnd
If shift <= acShiftMask Then
'This batch should be eliminated with or without shift.
Select Case keyCode
Case vbKeyA To vbKeyZ
VettedNumeric = lngNullChrcter_c
Case vbKeyMultiply To vbKeyAdd
VettedNumeric = lngNullChrcter_c
Case vbKeySubtract
VettedNumeric = lngNullChrcter_c
Case vbKeyDivide
VettedNumeric = lngNullChrcter_c
Case lngKeySemiColn_c To lngKeyHypnUscr_c
VettedNumeric = lngNullChrcter_c
Case lngKeyFwrdSlsh_c To lngKeyTldaGrve_c
VettedNumeric = lngNullChrcter_c
Case lngLeftBracket_c To lngRghtBracket_c
VettedNumeric = lngNullChrcter_c
Case Else
VettedNumeric = keyCode
End Select
'This batch should be eliminated only if shift alone is pressed.
If shift = acShiftMask Then
Select Case keyCode
Case vbKey0 To vbKey9
VettedNumeric = lngNullChrcter_c
Case lngPrdGrtrThan_c
VettedNumeric = lngNullChrcter_c
Case Else
VettedNumeric = keyCode
End Select
End If
Else
VettedNumeric = keyCode
End If
If Not silent Then
'Beep if illegal key was pressed.
If VettedNumeric = lngNullChrcter_c Then
VBA.Beep
End If
End If
Exit_Proc:
On Error Resume Next
Exit Function
Err_Hnd:
VBA.MsgBox "Error " & VBA.Err.Number & " (" & VBA.Err.Description & _
") in procedure VettedNumeric."
Resume Exit_Proc
End Function

[blackie42]

Thanks Aaron - much too much for my level of ability at the moment. I will keep and analyse when I get chance .

regards

Jon