PDA

View Full Version : Restrict entry to textbox



LouCziffra
09-25-2017, 03:45 AM
I am wrestling with this and several times thought I had it. What I need is code that restricts entry in a textbox on a userform to characters from 0 to 9, does not allow a leading 0 and does not allow values greater than 31. Does anyone have any ideas? It doesn't sound hard, but my poor brain is dripping with sweat after a day spent with this.

mana
09-25-2017, 03:59 AM
you can use combobox.

mdmackillop
09-25-2017, 04:01 AM
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
x = TextBox1.Value
If IsNumeric(x) Then
If Left(x, 1) = "0" Or CInt(x) > 31 Then
Me.TextBox1 = vbNullString
Cancel = True
Me.TextBox1.SetFocus
End If
Else
Me.TextBox1 = vbNullString
Cancel = True
Me.TextBox1.SetFocus
End If
End Sub

LouCziffra
09-25-2017, 04:41 AM
Thanks mana, but I want to use a textbox

Thanks also mdmackillop. I should have been more precise. What I am trying to achieve is code that will prevent the user from entering the invalid keystrokes. I can automatically remove the last digit that brings the value over 31 using Textbox1_Change and I can limit characters to 0:9 using Listbox1_KeyPress. This also allows me to block leading zeros. What I cannot do is combine the two approaches. Do you think this is possible?

mdmackillop
09-25-2017, 05:09 AM
I can see some point in checking each keystroke for a long text/numerical entry, but for two?

mdmackillop
09-25-2017, 05:21 AM
Dim tmp


Private Sub TextBox1_AfterUpdate()
tmp = TextBox1.Value
End Sub


Private Sub TextBox1_Change()
x = TextBox1.Value
y = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31)
On Error Resume Next
If IsError(Application.Match(CInt(x), y, 0)) Then
TextBox1.Value = tmp
End If
End Sub

Paul_Hossler
09-25-2017, 07:06 AM
Another possible approach



Option Explicit
Public N As Long

Private Sub CommandButton1_Click()
MsgBox N
Me.Hide
Unload Me
End Sub

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

Application.EnableEvents = False

If Len(Me.TextBox1.Text) = 1 And Chr(KeyCode) = "0" Then
Me.TextBox1.Text = vbNullString
GoTo NiceExit
End If
If Chr(KeyCode) > "9" Or Chr(KeyCode) < "0" Then
If Len(Me.TextBox1.Text) > 0 Then
Me.TextBox1.Text = Left(Me.TextBox1.Text, Len(Me.TextBox1.Text) - 1)
End If
GoTo NiceExit
End If
If CLng(Me.TextBox1.Text) > 31 Then
Me.TextBox1.Text = Left(Me.TextBox1.Text, Len(Me.TextBox1.Text) - 1)
GoTo NiceExit
End If

N = CLng(Me.TextBox1.Text)

NiceExit:
Application.EnableEvents = True

End Sub

Private Sub UserForm_Initialize()
Me.TextBox1.SetFocus
End Sub

LouCziffra
09-26-2017, 01:15 AM
Hi mdm. That's a really neat approach and if the internet were more advanced I'd send you a beer. As it is, you will have to settle for my eternal gratitude. I would never have thought of that solution and it works really well. My only two concerns are firstly that it is rather brutal in the way that it empties the textbox if you go too far whereas I prefer that is simply ignore the further key strokes. To this end, I've borrowed the code line from Paul Hossler
Me.TextBox1.Text = Left(Me.TextBox1.Text, Len(Me.TextBox1.Text) - 1)
in lieu of
TextBox1.Value = tmp
The other thing that bothers me slightly is that the key stroke sequence "1" "Backspace" "0" is not trapped and I can't think how to prevent that. I tried adding the line
If Left(x, 1).Value = "0" Then TextBox1.Value = Mid(x, 1, 2)
after "x" has been identified, but I get an error message "Object required". I don't understand this as if I replace the x with "1" the code line works.

Paul. I must be doing something wrong as I was unable to get your code to work. It kept trapping me and preventing my entering anything at all. Nonetheless, I have learnt some valuable new tricks from it and will keep trying to get it to work.

Once again, a big thankyou to both of you for your time and effort. It is most appreciated and I have learned a lot. As proverbs 9:9 says "Give instruction to a dumbo and he will be less dumb."

mdmackillop
09-26-2017, 05:14 AM
it is rather brutal in the way that it empties the textbox if you go too far
Is this gentler?

Private Sub TextBox1_Change()
x = TextBox1.Value
y = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31)
On Error Resume Next
If IsError(Application.Match(CInt(x), y, 0)) Then
Beep
Application.Speech.Speak "Can't you follow a simple instruction?"
End If
End Sub

LouCziffra
09-26-2017, 06:09 AM
Hi mdm. Love it. That's really great.