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


"BadgerMK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>

[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


"RB Smissaert" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote
>> in message news:[email protected]...
>>>
>>> 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
>>>
>>
>

[BadgerMK]

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

[RB Smissaert]

No trouble, I needed something very similar myself.

RBS

"BadgerMK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[RB Smissaert]

No trouble, I needed something very similar myself.

RBS

"BadgerMK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>