Auto decimal placement in textbox

[Nick Vittum]

Is there code that will make possible for a textbox in a userform (already formatted as currency) to automatically set the decimal place? (I mean, I'm sure with enough code anything is possible. But is there a standard and relatively simple way to do this?) I don't want to simply divide by 100. I want a method that will either allow the manual placement of the decimal, if that should happen by error, or else will return an error if that should happen.

thanks

 

[mikerickson]

Userform textboxes are not formatted like cells. They hold and show strings. Any alteration to that must be done by code by the programmer.

You say that your textbox is currency. I'll take that to mean that if the user enters 12345.89 you want the text box to show "$ 12,345.89".
I would use code like this to do that.

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim NumString As String
    Const CurrencyFormatString = "$ #,##0.00"
    NumString = Replace(Replace(Replace(TextBox1.Text, "$", vbNullString), ",", vbNullString), " ", vbNullString)
    If Not (IsNumeric(NumString)) Then
        Select Case MsgBox("Non numeric entry.", vbAbortRetryIgnore)
            Case Is = vbAbort
                TextBox1.Text = Format(Val(TextBox1.Tag), CurrencyFormatString)
                Exit Sub
            Case vbRetry
                Cancel = True
                Exit Sub
            Case vbIgnore
                TextBox1.Tag = TextBox1.Text
        End Select
    Else
            TextBox1.Tag = CStr(Val(NumString))
    End If
   
    TextBox1.Text = Format(TextBox1.Tag, CurrencyFormatString)
End Sub

Note that the Textbox's .Text and .Value properties will have a dollar sign and thousands separator (which doesn't play nice with arithmetic). The .Tag property of the textbox has the number (as text) that the user entered without any formatting. Note also, that by clicking Ignore, the user can force the Textbox to accept an non numberic entry (like "cat") which will also be in the .Tag property as well as the .Text and .Value properties.

 

[Nick Vittum]

Thank you, Mike. This code is really nice. Elegant, really. Unfortunately

1. I'd already found code that would accomplish what you've done here (although less graceful, I'm afraid— I might remove it and replace it with yours); but
2. It doesn't do what I was looking for. Instead, what I was looking for would take the entry "1234589" (no decimal point) and return "$ 12,345.89" —like an accounting calculator would do. But rather than simply dividing by 100, I was hoping for something that would either ignore a decimal point if the the user entered one erroneously, or failing that would return an error.

And again, I suspect anything is possible with enough code; in fact, clumsy as I am with this stuff, I might well be able to write some myself that would get me there eventually. But instead, I was imagining that I'm far from the only person in the world wanting this, and that there must be some relatively simple and even standardized way to accomplish it. But maybe not so, eh? What I forget, over and over, is that textboxes by nature hold strings, not numbers. I think Excel needs to add a "NumberBox" to its set of controls....

 

[mikerickson]

I can't work on that right now, but my thought is that
If the user puts a decimal point in the correct place, that should be accepted.
If the user omits all decimal points, I would highlight that in some fashion, but that would be because I don't work in accounting and I would want to be alerted if Excel was going to read "12345" as 123.45. Your users might not have that issue.

if the user puts a decimal point other than the third from right, the routine should demand correction.

 

[Nick Vittum]

Right, not worth the effort if it were to be complex. I just thought, since accounting calculators did work that way, back when people used calculators (or so I remember—actually now that I think about it, there was a switch so you could toggle the option on or off) that it might be a common thing to want. Or that maybe there was even a built-in function to do this. Anyway, thanks a lot. I really like the code you did post.

 

[mikerickson]

It depends on the users. If they are used to omitting the decimal point when they type, they are used to interpreting the result the right way. The trick about these conventions is to make it transparent to the experienced used and educate the new user to the conventions being used.

 

[Nick Vittum]

I have to confess—I am the user. I'm retired, and just dabble with this stuff for my own pleasure and to keep my brain working. I really love it—but I have so much to learn!

 

[mikerickson]

I'm also often my own user. (Darn them users can be stubborn)