Limit input to two decimal places
Solved
Hellodie45
-
SANA -
SANA -
Hello,
I am a beginner in VBA Excel and I am trying to limit the input of a user in a textbox (in a userform) so that they can enter only a decimal number with at most two digits after the decimal point.
Is it possible to block the input when the user enters more than two digits after the decimal?
I do not want to display a message box to alert the user but really block the input after two digits following the decimal.
I am not sure if this is feasible in VBA Excel.
So far, I have managed to allow only numeric format in my textbox and to accept a separator for the decimal with the ability to enter either a comma or a point, which will be automatically transformed into a comma.
Another small question: how to transform my Excel column with values entered through the textbox (thus as text format) into number format?
Thank you in advance for your help.
Have a nice day everyone.
Hellodie45
Configuration: Windows XP / Internet Explorer 6.0 / Excel 2003
I am a beginner in VBA Excel and I am trying to limit the input of a user in a textbox (in a userform) so that they can enter only a decimal number with at most two digits after the decimal point.
Is it possible to block the input when the user enters more than two digits after the decimal?
I do not want to display a message box to alert the user but really block the input after two digits following the decimal.
I am not sure if this is feasible in VBA Excel.
So far, I have managed to allow only numeric format in my textbox and to accept a separator for the decimal with the ability to enter either a comma or a point, which will be automatically transformed into a comma.
Another small question: how to transform my Excel column with values entered through the textbox (thus as text format) into number format?
Thank you in advance for your help.
Have a nice day everyone.
Hellodie45
Configuration: Windows XP / Internet Explorer 6.0 / Excel 2003
3 réponses
Hello,
Question 1 : limit input to 2 digits after the decimal point:
Using the "TextBox_Change" event like this:
Question 2 : Convert the TextBox value from String to "number": Here, since we are talking about decimal numbers, we will convert the "String" from the TextBox to "Double". When writing to the cell, instead of a simple: Cells(1, 1) = TextBox1 we add CDbl:
--
Best regards,
-- Every problem has a solution. If there is no solution, then where is the problem? --
Question 1 : limit input to 2 digits after the decimal point:
Using the "TextBox_Change" event like this:
Private Sub TextBox1_Change() If Right(TextBox1, 1) = "." Or Right(TextBox1, 1) = "," Then TextBox1.MaxLength = Len(TextBox1) + 2 End Sub
Question 2 : Convert the TextBox value from String to "number": Here, since we are talking about decimal numbers, we will convert the "String" from the TextBox to "Double". When writing to the cell, instead of a simple: Cells(1, 1) = TextBox1 we add CDbl:
Cells(1, 1).Value = CDbl(TextBox1)
--
Best regards,
-- Every problem has a solution. If there is no solution, then where is the problem? --
Hello,
You can try to truncate the entered number. For example, if the user enters 12.13264, when the text box is no longer selected, this number converts to 12.13
To truncate, you need to create a function that does that. There are several ways to proceed.
For example, number = int(number * 10^2) / 10^2 to get two digits after the decimal point.
to be placed in an event: TextBox1_Exit
--
C. Taha
You can try to truncate the entered number. For example, if the user enters 12.13264, when the text box is no longer selected, this number converts to 12.13
To truncate, you need to create a function that does that. There are several ways to proceed.
For example, number = int(number * 10^2) / 10^2 to get two digits after the decimal point.
to be placed in an event: TextBox1_Exit
--
C. Taha
Thank you for your response, but I would like to know if it's possible to limit input to two digits after the decimal point (for example, using a message box to warn the user that they are entering too many).
If that is not possible, I had already considered this solution, which puts a lot of responsibility on the user (which I am trying to avoid as much as possible).
But thanks again...
If that is not possible, I had already considered this solution, which puts a lot of responsibility on the user (which I am trying to avoid as much as possible).
But thanks again...
I also propose this solution:
Private Sub TextBox1_Change()
If InStr(1, Replace(TextBox1.Text, ",", "."), ".") * (InStr(1, TextBox1.Text, ".") + 2 < Len(TextBox1.Text)) Then TextBox1.Text = Left(TextBox1.Text, Len(TextBox1.Text) - 1)
End Sub
We still need to know if the decimal separator in your system is "." or ",". For me, it's the period ".". It needs to be adjusted accordingly, or improved to account for both cases.
--
C. Taha
Private Sub TextBox1_Change()
If InStr(1, Replace(TextBox1.Text, ",", "."), ".") * (InStr(1, TextBox1.Text, ".") + 2 < Len(TextBox1.Text)) Then TextBox1.Text = Left(TextBox1.Text, Len(TextBox1.Text) - 1)
End Sub
We still need to know if the decimal separator in your system is "." or ",". For me, it's the period ".". It needs to be adjusted accordingly, or improved to account for both cases.
--
C. Taha
If you're unsure about the type of decimal separator, do not use the Replace() function, which will result in this:
Private Sub TextBox1_Change()
If InStr(1, TextBox1.Text, ".") * (InStr(1, TextBox1.Text, ".") + 2 < Len(TextBox1.Text)) Then TextBox1.Text = Left(TextBox1.Text, Len(TextBox1.Text) - 1)
End Sub
Private Sub TextBox1_Change()
If InStr(1, TextBox1.Text, ".") * (InStr(1, TextBox1.Text, ".") + 2 < Len(TextBox1.Text)) Then TextBox1.Text = Left(TextBox1.Text, Len(TextBox1.Text) - 1)
End Sub
Thank you very much for worrying about me.
The solution Pijaku gave me works and seems a bit clearer to me. But if you think your solution is more effective, I will adopt it without hesitation.
Moreover, do you think you have a solution to avoid copying/pasting 25 times (according to Pijaku, there isn't)? Because it drives me crazy to have to do it! LOL
Thanks again...
The solution Pijaku gave me works and seems a bit clearer to me. But if you think your solution is more effective, I will adopt it without hesitation.
Moreover, do you think you have a solution to avoid copying/pasting 25 times (according to Pijaku, there isn't)? Because it drives me crazy to have to do it! LOL
Thanks again...
Hello
1 - For now, you can define a single method for all textboxes in the UserForm
Private Sub TextBox1_Change()
test Me.ActiveControl
End Sub
Private Sub test(txtObj As Object)
If Len(txtObj) = 0 Then txtObj.MaxLength = 255
If Right(txtObj, 1) = "." Or Right(txtObj, 1) = "," Then txtObj.MaxLength = Len(txtObj) + 2
End Sub
Use with caution. You need to verify that all TextBoxes truly have the same function on your form.
2 - Otherwise, for pijaku's solution, it's clever. But it has a small flaw:
When you write "1253.24", it's true, it blocks the decimals. However, you can no longer modify the integer part under this format. You can’t add a digit at the beginning like "91253.25" without first deleting a decimal digit. The solution I proposed takes this remark into account.
1 - For now, you can define a single method for all textboxes in the UserForm
Private Sub TextBox1_Change()
test Me.ActiveControl
End Sub
Private Sub test(txtObj As Object)
If Len(txtObj) = 0 Then txtObj.MaxLength = 255
If Right(txtObj, 1) = "." Or Right(txtObj, 1) = "," Then txtObj.MaxLength = Len(txtObj) + 2
End Sub
Use with caution. You need to verify that all TextBoxes truly have the same function on your form.
2 - Otherwise, for pijaku's solution, it's clever. But it has a small flaw:
When you write "1253.24", it's true, it blocks the decimals. However, you can no longer modify the integer part under this format. You can’t add a digit at the beginning like "91253.25" without first deleting a decimal digit. The solution I proposed takes this remark into account.
For the first problem, I will try to find a solution to reduce and optimize my code. Thank you for your solution.
For the second one, you are absolutely right, we cannot add digits after exceeding two decimal places. But I tried your code and it doesn't work (maybe I mishandled it ...). But anyway, it's not a big deal, the user can delete and rewrite.
Thank you for your help.
For the second one, you are absolutely right, we cannot add digits after exceeding two decimal places. But I tried your code and it doesn't work (maybe I mishandled it ...). But anyway, it's not a big deal, the user can delete and rewrite.
Thank you for your help.
Question 2 is resolved (I was overthinking it) and it was very simple once I know all the functions of VBA. A big thank you to you!
However, for question 1: it works partially because when I enter a decimal number with more than two digits after the comma, it locks and does not display the rest (which is what I want) but when I want to erase what I just entered and I input an integer (a bit long), it gets stuck.
Here’s an example:
I want to enter 1.235 and the input gets stuck at 1.23 (great, that’s what I want) but when I delete 1.23 and want to replace it with 123456, I can only enter 1234.
Do you see where the problem comes from?
Thank you again for your answers...
Thank you very much for your help!!!
One last little question: when I have several textboxes in the same userform with the same type of issue: blocking entry of a decimal with more than two digits after the decimal point, how should I avoid copying and pasting the code 25 times?
Thanks again ...
Uh ... you're welcome and see you soon.
Have a good day.