PDA

View Full Version : Solved: Limit input to textbox



blackie42
08-13-2009, 04:44 AM
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
08-13-2009, 05:20 AM
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

MaximS
08-13-2009, 05:33 AM
try to add below code to your User Form (change TextBox1 to name of your textbox):


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

mdmackillop
08-13-2009, 05:43 AM
I forgot about the 31 limit :oops:

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

mikerickson
08-13-2009, 06:07 AM
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

blackie42
08-13-2009, 06:53 AM
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
08-13-2009, 07:05 AM
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 TextBox2.SetFocus from my code.

blackie42
08-13-2009, 07:27 AM
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
08-13-2009, 07:28 AM
I see the end number is set to 33 - think if I chanfge to 32 it will work ok

thanks again

Jon

MaximS
08-13-2009, 08:56 AM
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.