Limit number of digits after comma in textbox

Question

I have a textbox in userform which will be filled only with digits, commas, or dots. I know how to restrict use of only those characters. My question is it possible to limit filling value to 2 digits after coma/dot?

So when I enter value like: 1023,456 it would not let me type 6 without any action.

Editted:
I can't get this... I tried testing codes given here: Regex to match 2 digits, optional decimal, two digits However it matches too many things. When I type more then 2 digits after comma it still matches as good string. I used for example:

\d{0,2}(\,\d{1,2})?
[0-9]?[0-9]?(\,[0-9][0-9]?)?

What I am doing wrong?

Private Sub netto_Change()

Dim regEx As New VBScript_RegExp_55.RegExp

regEx.Pattern = "\d{0,2}(\,\d{1,2})?"

If regEx.Test(netto.Value) = True Then MsgBox ("It works!")

End Sub

Edit 2:
Okay, I am really close I got this code: ^[0-9]+[\,\.]?[0-9]?[0-9]$ but one thing is missing. This pattern should also apply to string like: 321, with comma\dot at the end but without anything after that.

What to do?

Does a comma represent a thousands separator or a decimal point or neither in your application? — rajah9, Mar 17 '14 at 14:40
@rajah9 It's a decimal point. So it's a case of not adding more digits than it's possible - especially by accident. — lowak, Mar 17 '14 at 14:55
@Gary'sStudent So far I haven't thought of any way to achieve it. I am not really sure if it is even possible. — lowak, Mar 17 '14 at 14:57
VBA can use regex. See http://stackoverflow.com/questions/17609031/handling-series-of-comma-separated-values-in-vba/17609273#17609273 . If you change the .pattern so that it has a ",dd" then it would accept only 2 digits after the comma. — rajah9, Mar 17 '14 at 15:26

score 0 · Answer 1 · answered Mar 17 '14 at 14:51

0

The Textbox controls (there are at least two: ActiveX and UserForm) have events that can be used to QA the data.

On a UserForm:

    Private Sub TextBox1_Change()
        ' QA text here
    End Sub

There are other events, like KeyDown() and Exit() that may work better.

answered Mar 17 '14 at 14:51

rheitzman

2,247
3
20
36

Take a stab at it and make a new thread with your code. State what kind of textbox you are using and where it is: a control on a sheet, UserForm. – rheitzman Mar 17 '14 at 15:10

score 0 · Answer 2 · answered Mar 20 '14 at 14:32

This assumes you have event code to trap after the user completes entry to the TextBox. That code should call the following function. The function will return True if the string is good, False if the string is bad. Your code must decide how to handle False :

Public Function QualCheck(S As String) As Boolean
    Dim L As Long, CH As String, I As Long
    QualCheck = False
    L = Len(S)
    For I = 1 To L
        CH = Mid(S, I, 1)
        If CH Like "[0-9]" Or CH = "." Or CH = "," Then
        Else
            MsgBox "bad character " & CH
            Exit Function
        End If
    Next I
    If InStr(S, ".") + InStr(S, ",") = 0 Then
        QualCheck = True
        Exit Function
    End If
    If InStr(S, ".") > 0 Then
        ary = Split(S, ".")
        If Len(ary(1)) > 2 Then
            MsgBox "too many characters after ."
        Else
            QualCheck = True
        End If
        Exit Function
    End If
    If InStr(S, ",") > 0 Then
        ary = Split(S, ",")
        If Len(ary(1)) > 2 Then
            MsgBox "too many characters after ,"
        Else
            QualCheck = True
        End If
    End If
End Function

NOTE:

This code does not rely on regEx

Well, actually not `after` but `on change`. That's why 3rd digit after comma/dot cannot be typed or can be typed but must be deleted immediately. — lowak, Mar 20 '14 at 14:57
Thanks for your effort! If you have an idea it would be great to test it. — lowak, Mar 20 '14 at 20:52

score 0 · Accepted Answer · answered Mar 24 '14 at 09:19

I found some time to think and I came up with a different idea on how to cope with that.

First of all I used KeyPress event to prevent input of any characters different then 0-9, comma and dot. To make my code work as I wanted I added code to Change event. If sentence checks whether there is comma or dot in my texbox input. If it is, limits maxlength.

Private Sub netto_Change()

Dim znaki As Byte

znaki = Len(netto.Value)


If InStr(1, netto.Value, ".", vbTextCompare) > 0 Or InStr(1, netto.Value, ",", vbTextCompare) > 0 Then

    If netto.MaxLength = znaki + 1 Or netto.MaxLength = znaki Then

    Else
    netto.MaxLength = znaki + 2

    End If

Else
netto.MaxLength = 0

End If

End Sub

Private Sub netto_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Select Case KeyAscii

    Case Asc("0") To Asc("9")
    Case Asc(",")
    Case Asc(".")
    Case Else
    KeyAscii = 0

End Select

End Sub

Limit number of digits after comma in textbox

3 Answers3