Validate Textbox/Userform Number Range

  • Thread starter Thread starter Ingo
  • Start date Start date
I

Ingo

I have one question which might be pretty simple to answer but so far I didn
't get it. I'm using a standard macro from MS in order to validate the entry
in a textbox of a userform. It looks like this:

Private Sub TextBox1_Change()

Dim okstop As Boolean
Dim Ok As Boolean
Dim mytext As String

okstop = False

Do
mytext = TextBox1.Value
If TextBox1.Value > 1 And mytext <> "" Then
TextBox1.Value = "" 'Clears the TextBox

'Shows a message box that informs you that you typed
'something other than a number.
MsgBox ("Percentage required, please type only numbers in
between 0 and 1")
Else
okstop = True 'You typed a number in the TextBox.

End If

'Continue with the loop if you click Yes.
'Stop the loop if they typed a number in the TextBox.
Loop Until (Ok = vbOK) Or (okstop = True)

End Sub

This works pretty fine as I wanted the user to enter only positive
percentages which means a number in between 0 and 1 (min 0%, max100%). Other
non-numeric letters should not be entered, that works as well. Now I would
like to change the code slightly so user can enter numbers in between -1 and
1 (min -100%, max+100%) and still not allow non numeric letters. Can anybody
tell me how this can be done, I was playing around but it never worked out.
Thanks a lot!

P.S. Sorry one more question, as I have around 30 textboxes in one userform
do i have to repeat this code for every single textbox or is there a way to
do the validation for all with one code only?
 
You could try something like this
Private Sub TextBox1_LostFocus()
Dim mytext As String

mytext = TextBox1.Value
If Abs(TextBox1.Value) > 1 And mytext <> "" Then
TextBox1.Value = "" 'Clears the TextBox

'Shows a message box that informs you that you typed
'something other than a number.
MsgBox ("Percentage required, please type only numbers in between -1
and 1")
TextBox1.Activate
End If


End Sub

it activates only when you click away from the text box, but it returns you
there if you have entered an invalid number.
 
Hi Paul,

thanks a lot for the quick reply, but unfortunatly it doesn 't work. This is
the only macro I put this userform but nothing happens when I click away from
TextBox1, it doesn 't seem to validate the input. do you have any idea what
could be missing? Thanks!
 
Back
Top