Custom textbox in Userform

[BadgerMK]

Hi All

I'm looking to limit an entry into a text box in a userform to
AAA/AAA/NNN

A=Alpha
N=Numeric

Any help greatly appreciated

 

[RB Smissaert]

You will need to filter out the wrong KeyDown events depending on the
position in the textbox.
There might be a more clever way to do this with regular expressions, but
something like this will
get you on the right track:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)

If KeyCode <> 8 And KeyCode <> 9 And KeyCode <> 13 And _
KeyCode <> 35 And KeyCode <> 36 And KeyCode <> 37 _
And KeyCode <> 39 And KeyCode <> 46 Then
Select Case TextBox1.SelStart
Case 0, 1, 2, 4, 5, 6
If IsAlpha(Int(KeyCode)) = False Then
KeyCode = 0
Exit Sub
End If
Case 3, 7
If KeyCode <> 191 Or Shift <> 0 Then
KeyCode = 0
Exit Sub
End If
Case 8, 9, 10
If IsNumeric(Int(KeyCode)) = False Then
KeyCode = 0
Exit Sub
End If
Case Else
KeyCode = 0
Exit Sub
End Select
End If

End Sub

Function IsAlpha(iKeyCode As Integer) As Boolean
If iKeyCode > 64 And _
iKeyCode < 91 Then
IsAlpha = True
End If
End Function

Function IsNumeric(iKeyCode As Integer) As Boolean
If iKeyCode > 95 And _
iKeyCode < 106 Then
IsNumeric = True
End If
End Function

Just fiddle the numbers till you get it as you want.


RBS

 

[RB Smissaert]

OK, first attempt no good, but this is a better one, all code in the
Userform:


Option Explicit
Private strTemp As String
Private btPos As Byte

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)

strTemp = TextBox1
btPos = TextBox1.SelStart

End Sub


Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)

If IsValid(TextBox1) = False Then
TextBox1 = strTemp
TextBox1.SelStart = btPos
End If

End Sub


Function IsValid(strText As String) As Boolean

Dim i As Byte

If Len(strText) > 11 Then
Exit Function
End If

For i = 1 To Len(strText)
Select Case i
Case 1, 2, 3, 5, 6, 7
If Len(strText) > 1 Then
If IsAlpha(Mid$(strText, i, 1)) = False Then
Exit Function
End If
Else
If IsAlpha(strText) = False Then
Exit Function
End If
End If
Case 4, 8
If Asc(Mid$(strText, i, 1)) <> 47 Then
Exit Function
End If
Case 9, 10, 11
If IsNumeric(Mid$(strText, i, 1)) = False Then
Exit Function
End If
Case Else
Exit Function
End Select
Next

IsValid = True

End Function


Function IsAlpha(strText As String) As Boolean
If Asc(strText) > 64 And _
Asc(strText) < 123 Then
IsAlpha = True
End If
End Function


Function IsNumeric(strText As String) As Boolean
If Asc(strText) > 47 And _
Asc(strText) < 59 Then
IsNumeric = True
End If
End Function


RBS

 

[RB Smissaert]

Still no good, but this I think will do the job:


Option Explicit
Private strTemp As String
Private btPos As Byte

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)

strTemp = TextBox1
btPos = TextBox1.SelStart

End Sub

Private Sub TextBox1_Change()

If IsValid(TextBox1) = False Then
TextBox1 = strTemp
TextBox1.SelStart = btPos
End If

End Sub

Function IsValid(strText As String) As Boolean

Dim i As Byte

If Len(strText) > 11 Then
Exit Function
End If

For i = 1 To Len(strText)
Select Case i
Case 1, 2, 3, 5, 6, 7
If Len(strText) > 1 Then
If IsAlpha(Mid$(strText, i, 1)) = False Then
Exit Function
End If
Else
If IsAlpha(strText) = False Then
Exit Function
End If
End If
Case 4, 8
If Asc(Mid$(strText, i, 1)) <> 47 Then
Exit Function
End If
Case 9, 10, 11
If IsNumeric(Mid$(strText, i, 1)) = False Then
Exit Function
End If
Case Else
Exit Function
End Select
Next

IsValid = True

End Function

Function IsAlpha(strText As String) As Boolean
If Asc(strText) > 64 And _
Asc(strText) < 123 Then
IsAlpha = True
End If
End Function

Function IsNumeric(strText As String) As Boolean
If Asc(strText) > 47 And _
Asc(strText) < 59 Then
IsNumeric = True
End If
End Function


RBS

 

[BadgerMK]

Thank RBS, that has worked a treat, appreciate ur time and effort.

 

[RB Smissaert]

No trouble, I needed something very similar myself.

RBS