PDA

View Full Version : Solved: Better way to filter input?



nst1107
12-07-2008, 01:28 PM
I have been trying to find a fool-proof and convenient way to filter input coming through textboxes in userforms to make sure that only numbers are input. I have tried using the Like statement...

c = TextBox1 Like "#"
D = TextBox1 Like "##"
If TextBox1 = 0 Or c = False And D = False Then

...and setting a numeric (integer, in this case) variable equal to the textbox value and catching the possible error...

On Error GoTo noNumber
If TextBox1 = "" Then GoTo beginning
D% = TextBox1
beginning:

The latter seems to be the easiest to work with. Is there a better, more direct way? I've searched the help files and have come up blank.

malik641
12-07-2008, 01:47 PM
Hello,

You can use the KeyPress event for each textbox control that you want to make numeric only. By capturing this event, you make it so users can ONLY enter in numbers, and that way you don't have to check it afterward. I made the following code to be able to use decimal numbers too (just comment out "Case 46" and the IF statement if you want only numbers):

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
' numbers
Case 48 To 57
' decimal point
Case 46
' check if there already is a decimal in the textbox. if yes, cancel
If (InStr(1, Me.TextBox1.Value, ".") <> 0) Then KeyAscii = 0

' if neither of the above, cancel operation
Case Else
KeyAscii = 0
End Select
End Sub

EDIT: By the way, the KeyAscii variable is actually the ascii character code that the user entered. Here is a Ascii table for your reference:
http://enteos2.area.trieste.it/russo/IntroInfo2001-2002/CorsoRetiGomezel/ASCII-EBIC_files/ascii_table.jpg

nst1107
12-07-2008, 01:59 PM
...And I can add an If statement to control how big the number gets, too. Thanks. I'll try that. It will look more professional that what I was doing before.

malik641
12-07-2008, 02:03 PM
Yeah you can definitely do that. I would add it to the very beginning of the code, like:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (Len(Me.TextBox1.Value) > 10) Then
KeyAscii = 0
Else
Select Case KeyAscii
' numbers
Case 48 To 57
' decimal point
Case 46
' check if there already is a decimal in the textbox. if yes, cancel
If (InStr(1, Me.TextBox1.Value, ".") <> 0) Then KeyAscii = 0

' if neither of the above, cancel operation
Case Else
KeyAscii = 0
End Select
End If
End Sub

malik641
12-07-2008, 02:04 PM
Actually, don't do that. Use the MaxLength property of the text box itself in the VBE form design area.

nst1107
12-07-2008, 02:16 PM
Well, if I didn't want the value going over, say, 99, I could set the MaxLength property to 2, but if I didn't want it going over 100, I would have to use the If statement.

malik641
12-07-2008, 02:35 PM
Oh, I see. The actual value itself, not the number of characters. Then yeah, definitely use the If statement. Don't forget to convert the TextBox1.Value to a Long or Single like CSng(Me.TextBox1.Value) when making the comparison.

And welcome to board, by the way. :hi:

nst1107
12-07-2008, 02:50 PM
Actually, I'm still trying to figure this out. The KeyPress event doesn't occur after the character has been added to the TextBox, so I'm not sure how I would use this event to test the new value of the TextBox. Know what I mean?

The KeyUp event occurs after the TextBox value has been updated, but then we get more complicated.

Thanks for the welcome. :)

malik641
12-07-2008, 03:02 PM
Hmm..

Yeah I see what you mean because the user can have the cursor at the front of the text box or at the end. It's doable, but more of a headache than not. In situations like these, you usually validate the form before doing whatever action you are going to do (it should be the first set of code that runs when the user clicks "OK"). Then you evaluate the result, if it's greater than what you want, halt the execution, tell the user why they cannot proceed, then set the focus to that particular control (Me.TextBox1.Focus).

You could also wait until the user leaves the TextBox control using the Exit event and then evaulate the result if you need to check it right then and there.

What's the main goal of your userform?

nst1107
12-07-2008, 03:18 PM
The form essentially consists of a lot of text boxes and a lot of labels. The user will input a value into a text box, which will be inserted to a cell in a worksheet, some formulas will kick in, and a few lines of text will be spit out to the corresponding lable.

You can see why the input data needs to be validated before allowing the user to go on.

So, as of now, what I'm looking at is setting up an Enter event for each text box to store the current value (to revert to in case of bad input), and an Exit event to validate the input. This is doable, and I've done it before, but it would be great if there is a way with fewer steps.

malik641
12-07-2008, 03:45 PM
What if you had the text box like conditional formatting, and when the user goes over the value, you can turn the textbox red, and when they are in the clear, it can be white again? I'm assuming the users will know where the limits are before hand? Check this out.


Option Explicit

Const limit As Single = 99.99

Private Sub TextBox1_Change()
' check value

If (CSng(Me.TextBox1.Value) > limit) Then
' change value to red
Me.TextBox1.BackColor = vbRed
Else
' default white
Me.TextBox1.BackColor = &H80000005
End If
End Sub


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
' check value
If (CSng(Me.TextBox1.Value) > limit) Then
' tell user to fix
MsgBox "The value '" & Me.TextBox1.Value & "' is too high." & vbNewLine & vbNewLine _
& "Set the value below '" & limit & "'.", vbCritical

Cancel = True
End If
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57
' numbers
Case 46
' decimal point
' check if there already is a decimal in the textbox. if yes, cancel
If (InStr(1, Me.TextBox1.Value, ".") <> 0) Then KeyAscii = 0

Case Else
' if neither of the above, cancel operation
KeyAscii = 0
End Select
End Sub
And if you have to evaluate the number in the worksheet first, just change the TextBox_Change() event code and validate the number there.

nst1107
12-07-2008, 03:56 PM
You know, what? The idea was to make things less complicated, but I like what you've got there. I think I'll use it. Thanks for all your help.

malik641
12-07-2008, 04:05 PM
No problem :thumb

Hope things work out as intended.

malik641
12-07-2008, 04:22 PM
For what it's worth, to try to make things a little less complicated since you have many textboxes, you can do something like this:

Option Explicit

Const limit1 As Single = 99.99
Const limit2 As Single = 150.445

Private Sub TextBox1_Change()
Call TextBoxChange(Me.TextBox1, limit1)
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = TextBoxExit(Me.TextBox1, limit1)
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
KeyAscii = KeyPressMain(KeyAscii)
End Sub


Private Sub TextBox2_Change()
Call TextBoxChange(Me.TextBox2, limit2)
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = TextBoxExit(Me.TextBox2, limit2)
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
KeyAscii = KeyPressMain(KeyAscii)
End Sub



' ALL textboxes can reference these procedures in their own events
' and all of the code needed is in one place

Private Function KeyPressMain(ByVal KeyAscii As MSForms.ReturnInteger) As Integer
' returns the KeyAscii if passes validation

Select Case KeyAscii
Case 48 To 57
' numbers
Case 46
' decimal point
' check if there already is a decimal in the textbox. if yes, cancel
If (InStr(1, Me.TextBox1.Value, ".") <> 0) Then KeyAscii = 0

' if neither of the above, cancel operation
Case Else
KeyAscii = 0
End Select

KeyPressMain = KeyAscii
End Function

Private Function TextBoxExit(ByRef txtbox As MSForms.textbox, ByVal theLimit As Single) As Boolean
' returns Cancel if the value of the textbox doesn't conform with the limit
TextBoxExit = False

' check value
If (CSng(txtbox.Value) > theLimit) Then
' tell user to fix
MsgBox "The value '" & txtbox.Value & "' is too high." & vbNewLine & vbNewLine _
& "Set the value below '" & theLimit & "'.", vbCritical

TextBoxExit = True
End If
End Function

Private Sub TextBoxChange(ByRef txtbox As MSForms.textbox, ByVal theLimit As Single)
' gene

If (CSng(txtbox.Value) > theLimit) Then
' change value to red
txtbox.BackColor = vbRed
Else
' default white
txtbox.BackColor = &H80000005
End If
End Sub

nst1107
12-07-2008, 06:24 PM
Well, thanks again, Joseph, for all your help. Here's the finished product. It's fool-proof, it's compact, and it looks real professional when it executes.
Dim initial As String
Private Function KeyPressMain(ByVal KeyAscii As MSForms.ReturnInteger) As Integer
'Credit: Joseph (malik641<http://vbaexpress.com/forum/member.php?u=2828>)
'Discussion: <http://vbaexpress.com/forum/showthread.php?t=24053>
Select Case KeyAscii
Case 48 To 57
Case Else
KeyAscii = 0
End Select
KeyPressMain = KeyAscii
End Function
Private Sub TextBoxChange(ByRef txtbox As MSForms.TextBox)
On Error GoTo notAcceptable
If CInt(txtbox) > 100 Then GoTo notAcceptable
initial = txtbox
Exit Sub
notAcceptable:
If txtbox = "" Then Exit Sub
txtbox = initial
End Sub
Private Function TextBoxExit(ByRef txtbox As MSForms.TextBox, ByRef lbl As MSForms.Label) As Boolean
TextBoxExit = False
If txtbox = "" Then
TextBoxExit = True
Exit Function
End If
Range(txtbox.Tag) = CInt(txtbox)
lbl.Caption = Range(txtbox.Tag).Offset(, 5)
End Function
Private Sub Box1a_Enter() 'Prepares data validation
initial = Box1a
End Sub
Private Sub Box1a_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) 'Prevents any non-integer
KeyAscii = KeyPressMain(KeyAscii)
End Sub
Private Sub Box1a_Change() 'Keeps value <= 100, as well as validates pasted data
TextBoxChange Box1a
End Sub
Private Sub Box1a_Exit(ByVal Cancel As MSForms.ReturnBoolean) 'One last check to see if textbox is blank, then completes the process
Cancel = TextBoxExit(Box1a, L1a)
End Sub

malik641
12-07-2008, 06:44 PM
You're very welcome :) glad to help.