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


--
BadgerMK
------------------------------------------------------------------------
BadgerMK's Profile: http://www.excelforum.com/member.php...o&userid=31406
View this thread: http://www.excelforum.com/showthread...hreadid=518750

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


"BadgerMK" wrote in
message ...

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


--
BadgerMK
------------------------------------------------------------------------
BadgerMK's Profile:
http://www.excelforum.com/member.php...o&userid=31406
View this thread: http://www.excelforum.com/showthread...hreadid=518750

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" wrote in message
...
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


"BadgerMK" wrote
in message ...

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


--
BadgerMK
------------------------------------------------------------------------
BadgerMK's Profile:
http://www.excelforum.com/member.php...o&userid=31406
View this thread:
http://www.excelforum.com/showthread...hreadid=518750

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


"RB Smissaert" wrote in message
...
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" wrote in message
...
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


"BadgerMK" wrote
in message ...

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


--
BadgerMK
------------------------------------------------------------------------
BadgerMK's Profile:
http://www.excelforum.com/member.php...o&userid=31406
View this thread:
http://www.excelforum.com/showthread...hreadid=518750

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


--
BadgerMK
------------------------------------------------------------------------
BadgerMK's Profile: http://www.excelforum.com/member.php...o&userid=31406
View this thread: http://www.excelforum.com/showthread...hreadid=518750

No trouble, I needed something very similar myself.

RBS

"BadgerMK" wrote in
message ...

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


--
BadgerMK
------------------------------------------------------------------------
BadgerMK's Profile:
http://www.excelforum.com/member.php...o&userid=31406
View this thread: http://www.excelforum.com/showthread...hreadid=518750