ValueBox? or force TextBox to read/format value

[Leo Elbertse]

This problem has me stumped, actually for many years already:

Is there no way to force TextBox to receive values only AND format
these immediately in the TextBox. I don't mean numeric text, I mean
truly values. (Or am I actually daft and does something like ValueBox
exist)

e.g.

I enter: 123456
TextBox shows: 123,456.00

I enter: 1234.56 (i.e. a point as seperator)
TextBox shows: 1,234.56

I enter 1234,56 (i.e. a comma as seperator)
TextBox shows: 1,234.56 (i.e. same as above)


Especially due to the difference in puntuation between 'english' and
'non-english' numbers I frequently run into trouble.

When entering numbers in an Excel worksheet itself, Excel recognises
that a point actually is a comma in 'non-english' numbers, thus:

123.45 translates in 123,45

When entering the same in a TextBox however it doesn't do the same,
thus:

123.45 translates in 123

To get round the problem I use Replace(TextBox1.Value, "." , ",") but
this is rather a kludge AND what if the user actually enters a number
in proper thousands notation: 1.234,56 than this creates havock.

I simply cannot believe there is no easy solution, but I wouldn't know
how.

Thanks for any assistance,

Leo

 

[Juan Pablo González]

To get round the problem I use Replace(TextBox1.Value, "." , ",") but
this is rather a kludge AND what if the user actually enters a number
in proper thousands notation: 1.234,56 than this creates havock.

I simply cannot believe there is no easy solution, but I wouldn't know
how.

Thanks for any assistance,

Leo

Part of the problem is solved by using these values:

?Application.International(xlDecimalSeparator)
..
?Application.International(xlThousandsSeparator)
,

that way, you can "know" what the user tried to enter in his "own" language.
Then, convert it to a "real" number. What I do is something like

St = InputBox("Enter the number")
St = Replace$(St, Application.International(xlThousandsSeparator), "")
'Remove the comma
St = Evaluate(Chr$(34) & St & Chr$(34) & "+0")

to let Excel handle the conversion ! instead of reinventing the wheel...

 

[Leo Elbertse]

Thanks,

Unfortunately many users knowingly (or maybe even unknowingly) use the
wrong separator, because they use U.S. International keyboard setting,
so issues like the german 'u-umlaut' are easy to overcome.
Unfortunately this means the decimal seperator on the numberpad
becomes a point.

So maybe what I really need is a European International keyboard ;-)

Leo