Auto decimal placement in textbox

Nick Vittum

New Member
Joined
Apr 2, 2020
Messages
26
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,958
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.
VBA Code:
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

New Member
Joined
Apr 2, 2020
Messages
26
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,958
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

New Member
Joined
Apr 2, 2020
Messages
26
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,958
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

New Member
Joined
Apr 2, 2020
Messages
26
Office Version
  1. 365
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,303
Messages
5,623,865
Members
415,997
Latest member
ragomes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top