User Friendly Input mask/Format for Decimal number

[gbscobel]

I have what I think is an extremely simple question but I can't for the life of me figure out an answer. I have a form that a user wants to enter currency amounts in but I don't want the user to be able to enter more than 2 decimal places in the number and there is no absolute number of digits that will precede the decimal place. My problem is whenever I try to use an input mask, I can limit the decimals to two but the number entry before the decimal is always awkward in that it displays either blank spaces after the number you're typing so it looks weird or I'm forced to hard code the number of digits before the decimal.

Is there any way to format or use an input mask on a number field so that it allows the user to enter whatever number they want but won't allow them to enter more than 2 digits after a decimal point is entered?

 

[Remou]

Have you considered formatting:
Format(x,"#.00")
or rounding:
Round (x,2)
in the After Update event, rather than using an Input Mask?

 

[gbscobel]

Remou,

I appreciate your response. However, this does not address my problem. I need to limit the number of decimals allowed to only 2 and no more. The format or round command only truncate the value as entered and does not prevent the user from entering the extra decimals. Also, I don't want to round, I want the user to only be able to enter in 00 through 99 cents and not accidentally enter more to throw off future calculations.

 

[Remou]

Perhaps:

Private Sub txtText_KeyPress(KeyAscii As Integer)
If Len(Trim(Mid(txtText.Text, InStr(txtText.Text, ".") + 1))) >= 2 _
    And KeyAscii > 47 And KeyAscii < 58 _
    And Me.txtText.SelStart >= InStr(txtText.Text, ".") Then
    KeyAscii = 0
End If
End Sub

 

[gbscobel]

Thanks, that works almost perfectly. The only problem is it limits me to no more than 2 digits before the decimal place and I would need at least 3. I'm not sure I fully understand what your code does, how would I modify it to allow for more characters in front of the decimal.

 

[Remou]

I made the unwarranted assumption that the text would contain a decimal.

Private Sub txtText_KeyPress(KeyAscii As Integer)
'If there is a decimal in the text ...
If InStr(txtText.Text, ".") > 0 Then
   'If the length of the decimal portion of the text is
   'greater than or equal to 2 and if a number (0 to 9)
   'has been typed and if the character that has been 
   'typed is after the decimal point ...
   If Len(Trim(Mid(txtText.Text, InStr(txtText.Text, ".") + 1))) >= 2 _
       And KeyAscii > 47 And KeyAscii < 58 _
       And Me.txtText.SelStart >= InStr(txtText.Text, ".") Then
       'Ignore it.
       KeyAscii = 0
   End If
End If
End Sub

 

[gbscobel]

Thank you, that seems to work great. Sorry to be a pest but I noticed that although the field does not allow any digits to be entered after the second decimal, it does allow the entry of any text character (although it errors upon validation of the data since it's a currency field). Is there anything we can add to this code to prevent ANY additional characters from being entered after the second decimal? I really appreciate all your help.

 

[Remou]

You can play around with the characters that are not allowed here:
[tt]And KeyAscii > 47 And KeyAscii < 58[/tt]
Or rather than disallow, allow editing characters (backspace etc)