Restricting TextBox Numeric Range

[rameezl17]

Hi Everyone,

I have TextBox1 in a userform. When somebody opens up the userform the textbox is prepopulated with a number already in it (.31), however I did leave the option for the user to change this number if needed. The reason why there's a decimal because when the number is submitted on the userform it populates as a percentage in a excel worksheet. I am trying to make it so that a user can not input a number greater than .100 (100%) in the textbox.

I have the code below but I think the decimals messes up my code for whatever reason? Also when I go to change the .31 to a random number, as soon as I type in (.1) the error message pops up, even if the value is supposed to be (.15) which is in the correct range. However the message should only pop up if the user inputs a number like (.101). Is there a way to fix this? Thank you for your help in advance!

Private Sub TextBox1_Change()
If TextBox1.Value > -0.01 And TextBox1.Value > 0.101 Then
TextBox1.Value = TextBox1.Value
Else
MsgBox "Number Range Must Be Between 0 - 100%, Can Not Exceed 100%."
End If
End Sub

 

[jproffer]

Couple things...

You show a greater than sign for both conditions of your textbox value. Is that correct? Greater than -0.01 AND greater than 0.101?

Also, 100% would be 1.00 not 0.100 (10%). Which one do you want to use?

 

[Rick Rothstein]

Hi Everyone,

I have TextBox1 in a userform. When somebody opens up the userform the textbox is prepopulated with a number already in it (.31), however I did leave the option for the user to change this number if needed. The reason why there's a decimal because when the number is submitted on the userform it populates as a percentage in a excel worksheet. I am trying to make it so that a user can not input a number greater than .100 (100%) in the textbox.

I have the code below but I think the decimals messes up my code for whatever reason? Also when I go to change the .31 to a random number, as soon as I type in (.1) the error message pops up, even if the value is supposed to be (.15) which is in the correct range. However the message should only pop up if the user inputs a number like (.101). Is there a way to fix this? Thank you for your help in advance!

Private Sub TextBox1_Change()
If TextBox1.Value > -0.01 And TextBox1.Value > 0.101 Then
TextBox1.Value = TextBox1.Value
Else
MsgBox "Number Range Must Be Between 0 - 100%, Can Not Exceed 100%."
End If
End Sub

Floating point numbers do not have trailing zeroes, so 0.100 is the same as 0.1 which, as a percent, is 10%. For 100%, you would using the integer number 1. Also, the highlighted line of code is not testing correctly (even after you change 0.101 to 1.01... that second greater than symbol should be a less than symbol). And I am not sure the TextBox Change event is the best place for the code since your test will be performed after each keystroke (including the lone decimal point at the start). I would think a separate "commit" button which the user would click when they have finished inputting values in the UserForm's TextBox (and other input controls, if any) would be a better place to test the control's content.

 

[rameezl17]

Hi Jproffer & Rick,

So I fixed the range you guys were right that was a typo sorry about that so now the range is

TextBox1.Value > -0.1 And TextBox1.Value < 1

Now that it works when I override the original value from the textbox .31 the message does not pop up. However is there a way to make it so that the user can not click out of the textbox until the number is between the correct range?

 

[Rick Rothstein]

Now that it works when I override the original value from the textbox .31 the message does not pop up. However is there a way to make it so that the user can not click out of the textbox until the number is between the correct range?

Use the TextBox's Exit event procedure and set Cancel=True if you want to keep the user in the TextBox.

 

[rameezl17]

When I try the Cancel = True.. even if I type in a correct range number I still cant get out of the textbox? And My submit or exit buttons do not work when I putt he Cancel = True in my code?

 

[Rick Rothstein]

When I try the Cancel = True.. even if I type in a correct range number I still cant get out of the textbox? And My submit or exit buttons do not work when I putt he Cancel = True in my code?

I think you will have to show us your code in order for us to determine why you get stuck in your TextBox.

 

[rameezl17]

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1.Value > -0.01 And TextBox1.Value < 1 Then
TextBox1.Value = TextBox2.Value
Else
MsgBox "Number Range Must Be Between (.01 - 1), Can Not Exceed 1."
End If
Cancel = True
End Sub

So if I were to type in .25 in TextBox1, the textbox is still locked.

 

[Rick Rothstein]

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1.Value > -0.01 And TextBox1.Value < 1 Then
TextBox1.Value = TextBox2.Value
Else
MsgBox "Number Range Must Be Between (.01 - 1), Can Not Exceed 1."
End If
Cancel = True
End Sub

Where you put the Cancel=True makes it cancel the exit for all operations. You need to put it in the If..Then code block where the test fails so that you are blocked from exiting only when the test fails (which should be your Else block if I read your intent correctly).

 

[rameezl17]

It now works perfect! Thank you very much!!!