Solved: Limit input to textbox

[blackie42]

Hi

How can I limit the user input in a UF textbox to a certain range e.g.

01 -> 31 (need the 0 in front of 1,2 3, etc as I want to auto tab to next TB once a valid 2 digit input is achieved) with message if incorrect input e.g 32

Tried a few things but can't seem to crack it

thanks for any help

Jon

[mdmackillop]

[VBA]Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If IsNumeric(TextBox1) And Len(TextBox1) = 2 Then
'do nothing
Else
Cancel = True
MsgBox "Please enter 2 digit number"
TextBox1 = ""
End If
End Sub[/VBA]

[MaximS]

try to add below code to your User Form (change TextBox1 to name of your textbox):

[VBA]
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim temp As String, err As String

If Len(Me.TextBox1.Text) = 1 And CInt(Me.TextBox1.Text) > 0 Then
temp = "0" & Me.TextBox1.Text
Me.TextBox1.Text = temp
Else
If CInt(Me.TextBox1.Text) >= 0 Then
If CInt(Me.TextBox1.Text) > 32 Then
err = MsgBox("Please enter number between 0 and 32", _
vbCritical + vbOKOnly, "Wrong Input")
End If
Else
err = MsgBox("Please enter number between 0 and 32", _
vbCritical + vbOKOnly, "Wrong Input")
End If
End If

End Sub
[/VBA]

[mdmackillop]

I forgot about the 31 limit
[VBA]
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If IsNumeric(TextBox1) And Len(TextBox1) = 2 And _
CInt(TextBox1) > 0 And CInt(TextBox1) < 31 Then
'do nothing
Else
Cancel = True
MsgBox "Please enter 2 digit number between 1 & 31"
TextBox1 = ""
End If
End Sub

[/VBA]

[mikerickson]

[VBA]Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Len(TextBox1.Text) = 0 Then
Select Case Chr(KeyAscii)
Case Is = "1", "0", "2", "3"
Case Else
KeyAscii = 0
End Select
Else
Select Case Chr(KeyAscii)
Case Is = "1", "0", "2", "3", "4", "5", "6", "7", "8", "9"
If 0 < Val(TextBox1.Text & Chr(KeyAscii)) And Val(TextBox1.Text & Chr(KeyAscii)) < 33 Then
TextBox1.Text = TextBox1.Text & Chr(KeyAscii)
KeyAscii = 0
TextBox2.SetFocus
Else
KeyAscii = 0
End If
Case Else
KeyAscii = 0
End Select
End If
End Sub[/VBA]

[blackie42]

Thanks Guys,

Mike, I already have a 'change' sub for the textbox which activates the next textbox and label - is there anyway to incorporate this - your code works but doesn't light up the next TB

Max - couldn't get your code to work - fails on the first if statement

thanks

[mikerickson]

I'd need to see the existing Change event to see how to incorperate it. But if that Change event handles the "go to next textbox" part of your requirements, try removing [VBA]TextBox2.SetFocus[/VBA] from my code.

[blackie42]

Yep - removing it from your code does allow it to work.

Just one slight problem - it does allow 32 to be entered

I will try to work out how your code handles this set of numbers so I can apply it to the next set which will be 01 through to 12

thanks

[blackie42]

I see the end number is set to 33 - think if I chanfge to 32 it will work ok

thanks again

Jon

[MaximS]

Hi blackie,

the only reason i see why it might not work is fact you testing that code with nothing typed in the text box.