Userform Textbox Formatting

[LSM1604]

Hi all,

I'm a bit stuck with one of my userforms. I have a username textbox on my workbook logon screen. I have put a max length on the textbox to 7 characters. I have also added a bit of code which stops the user from entering anything less than 7 characters (once the commandbutton is clicked, a msgbox comes up), and the textbox will show text in upper case.

What I would like is a way to "format" the textbox. All of the usernames will be in the following format: BBAAA11. The first 2 characters will always be "BB". The 3rd, 4th and 5th characters will be any combination of letters (for example... "CAM", "EAL", "BEF"). Finally the last 2 characters will always be any combination of 2 digits (for example... "76" "24", "00").

If someone could provide me with some code which will not allow the textbox to type anything other than the specified format, or for a msgbox to appear once the commandbutton has been clicked to say that the incorrect format has been entered, I will be most grateful.


Thanks,

LSM

*cross posted @ http://www.mrexcel.com/forum/showthr...44#post2198644*

[aron]

By using the character codes to check weather or not the parts of the string is number or letters. Simply make str to whatever is in the box.
This should probably do it.

Sub IllegalEntry()
Dim str As String
Dim Ok As Boolean

    Ok = True

        str = "The string you want to check"
        If Not Left(str, 2) = "BB" Then Ok = False
    
        
        For t = 3 To 5
            If Asc(Mid(str, t)) < 64 Then Ok = False
            If Asc(Mid(str, t)) > 91 Then Ok = False
            Cells(i, t) = (Mid(str, t))
        Next t
        
        For t = 6 To 7
            If Asc(Mid(str, t)) < 48 Then Ok = False
            If Asc(Mid(str, t)) > 57 Then Ok = False
        Next t
    
    If Ok = False Then
    '  <What you want to happen if it's an illegal entry>
    End If

End Sub

[LSM1604]

Excellent! Thanks to you, I will have no more head scratching over that problem!

Thanks, much appreciated.

LSM

[Marcol]

Possibly try this? It should make wrong entries difficult!
A few tweeks and it could be bullet proof!

Add to your userform code, change the Textbox name to suit your needs.

Private Sub TextBox1_Enter()
    TextBox1.Value = "BB"
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(TextBox1.Value) < 7 Then
        Cancel = True
    End If
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case True
        Case Len(TextBox1.Value) >= 2 And Len(TextBox1.Value) <= 4
            If KeyAscii >= 97 And KeyAscii <= 122 Then
                KeyAscii = KeyAscii - 32
            ElseIf KeyAscii >= 65 And KeyAscii <= 90 Then
                KeyAscii = KeyAscii
            Else
                KeyAscii = 0
            End If
        Case Len(TextBox1.Value) > 4 And Len(TextBox1.Value) <= 6
            If KeyAscii < 48 Or KeyAscii > 57 Then
                KeyAscii = 0
            End If
        Case Else
            KeyAscii = 0
    End Select
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If Len(TextBox1.Value) < 7 Then
        Cancel = True
    End If
End Sub

You might want to add msgboxes to explain entry errors.

[LSM1604]

Hi Marcol,

Thank you for taking the time to answer my post. Aron just pipped you to the post with a solution!

LSM

[aron]

Oh, forgot to say that this assumes that the text entered is in uppercase. It will get marked as wrong if it's "bb" instead of "BB" seems like Marcols code deals with both upper and lower case.

[LSM1604]

Hi all,

The textbox is forced into upper case sing the following code:

Private Sub TextBox_BB_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub

Thanks for all your help.

LSM

[Marcol]

No problem!

None the less please have a look at the code - it demonstrates a stricter control over user entries, and the methods can be applied to any situation!

Cheers.