Validate Textbox/Userform Number Range

[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?

 

[Paul C]

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.

 

[Ingo]

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!