Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate Textbox/Userform Number Range
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate Textbox/Userform Number Range
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! "Paul C" wrote: 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. -- If this helps, please remember to click yes. "Ingo" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validate Textbox/Userform Number Range | Excel Programming | |||
Number format of textbox in userform | Excel Programming | |||
Userform textbox number formats | Excel Discussion (Misc queries) | |||
Number Formats In A UserForm TextBox | Excel Programming | |||
Formatting number in a UserForm TextBox | Excel Programming |