Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ValueBox? or force TextBox to read/format value
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ValueBox? or force TextBox to read/format value
Leo Elbertse wrote:
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... -- Regards, Juan Pablo González |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ValueBox? or force TextBox to read/format value
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 On Mon, 12 Apr 2004 11:09:17 -0500, "Juan Pablo González" wrote: 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Force excel to read dates as D/M/YYYY | Excel Worksheet Functions | |||
Force read-only in auto_open macro | Excel Discussion (Misc queries) | |||
Force format change in excel | Excel Worksheet Functions | |||
Force File Read-Only? | Excel Programming | |||
Force Read-Only? | Excel Programming |