rameezl17

Board Regular
Joined
Mar 6, 2018
Messages
105
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
 
Last edited:

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Mar 6, 2018
Messages
105
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Mar 6, 2018
Messages
105
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Mar 6, 2018
Messages
105
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,108,939
Messages
5,525,741
Members
409,661
Latest member
pprabha

This Week's Hot Topics

Top