Restrict entry to textbox

[LouCziffra]

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]

you can use combobox.

[mdmackillop]

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]

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]

I can see some point in checking each keystroke for a long text/numerical entry, but for two?

[mdmackillop]

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]

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]

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]

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]

Hi mdm. Love it. That's really great.