Localisation decimal problems
 

Hello,
Ive run into a strange problem in connection with using decimal values.
I asked about it before but nobody replied to it but it really bothers me as
I cannot find a solution. Or maybe I have found the fault and in
contradiction to what have been written previously I dare to say that
WindowsXP or Excel2003 has a problem with the localisation!
I have written a registration program for purchase, orders and invoices.
The registration is done in a Form that is connected to a workbook.
I have different functions to save and retrieve previously registered
documents
and the heart of the processing is a 2 column, 30 row part of a spreadsheet
where all different values are registered. These Cells are connected to the
Form via the .Controlsource property of Textboxes, ComboBoxes, etc.
None of the controls are linked via variables, the connection is quite
straight, nor does the Form code much of calculation, this is mostly done by
the spreadsheet and any resulting values are presented in Labels in the form.
So that is basically what is done, I put in the parameters like quantity,
price and currency, the spreadsheet calculates and the result is returned and
presented.
My problem concern one of the cells and its connected Textbox, the Quantity!
Rightfully, its four cells, because I have quadrupled the rows and then Im
able to register 4 lines in the purchases/order etc.
How stupid it ever sounds:
If I put a QUANTITY IN THE SPREADSHEET BEFORE STARTING THE FORM (f.i. when
reading in an old document), with decimal value other than 0, e.g. 4,1 AND
THEN LOAD THE FORM, Excel runs up over 50% CPU usage and mostly it hangs!
If the value is 4,0 it starts/runs fine! If I have a purchase-price with
decimals, there is no problem either, 286,70 works fine! If I register a
decimal-value IN the form it works as well. The problem occurs if I read back
a document or start the form with a decimal value above 0. There is no
difference between these two textboxes, they are set up in exactly the same
manner and they are even adjacent to each other within the same frame in the
form:
In the Userform_Initialize () part
Worksheets("System").Activate
TxB404.ControlSource = "QY1"
PuTxB405.ControlSource = "PP1"
and so on€¦€¦.
Later in the code where the responses to the input take place I have only a
simple code part for these two:
Public Sub TxB404_AfterUpdate()
OSumUpdate
End Sub
Public Sub PuTxB405_AfterUpdate()
OSumUpdate
End Sub

The call to OSumUpdate is used to that every change of a value will cause a
recalculation of the underlying spreadsheet and I want the change to be
reflected in the result -€œlabels€� in the form like:
PuLa408.Caption = Format(Range("PVAL1"), "##,##0.00")
This is the resulting purchase value from my quantity and price above.

I cannot solve it. But finally I changed the localisation to English (UK)
where decimals are . (periods) not , (commas) as we do in Sweden. (I have
English versions of both Windows XP Professional SP2,
and Office 2003 Professional, SP2)
THE PROBLEM DISAPPEARED!
I can use whatever numbers with decimals, I like and the code works
excellently!
What is wrong, my programming or my programs!

Ive posted this question to both the Excel Programming and Suggestion for
Microsoft.
I hope someone can solve it!
Best regards
Mats


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

Localisation decimal problems
 

Mats.

Afraid your "rant" is old news :(

MSForms and VBA are US centric, and have been since conception.
(and MSforms hasn't changed since 97)

Numeric or date input in textboxes has always been a hassle.
Working with controlsource doesn't make it any easier,
since it's linked to Formula rather than FormulaLocal property

And in this case it's only a decimal... not dates etc.

A few tips:

control source.
No need to Activate worksheet "system" if you specify the controlsource
as the EXTERNAL (fully qualified) address of the range.

e.g. Me.Textbox1.ControlSource =
wksSys.Range("a1").address(external:=true)

To keep your coding to a minimum you can opt to force
any "," to a "." with a simple routine:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Application.DecimalSeparator < "." Then
If KeyAscii = Asc(Application.DecimalSeparator) Then
KeyAscii = Asc(".")
End If
End If
End Sub

If you've never worked with class modules then you'll have to repeat
this event handler for all "number" textboxes, else you could set up 1
class for all the boxes.


The call to oSumUpdate is not needed.
The label's controlsource can be set to a cell with a sum formula.
...and if you make that formula output a formatted STRING
with the TEXT worksheetfunction. e.g. =TEXT(SUM(A1:A3),"#,##0.00")
the formatting is taken care of.


HTH.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

Localisation decimal problems
 

So what the heck have they been doing in Seattle since -97 then?
I hope something good would happen after "conception", you have to care for
your baby after birth as well!! Ive searched through the community but there
are no proper solutions only workarounds. Even Access has a TextBox that can
have a number OR date format and a decimal property too. I dont know if
theyve solved this problem in Access, but it sure looks much more thoroughly
done!
So maybe you can €œrant€� and €œvine€� together with me so they start
programming! :)

ControlSource: I dont see your point! There are 60 controls with
controlsources
to the same worksheet, €œSystem€�. Why write the address for every control.

Comma replacement: I dont feel sure about such a workaround. My workbook is
an €œadministrative system€� and relatively comprehensive with an advanced
calculation part, possibilities to save numerous business calculations,
transfer offers to orders and purchases, invoice order confirmations,
call-off from running contracts, etc. Pretty much of it starts from a
previously saved calculation, order, purchase, etc and replacing all commas
to periods throughout the system while still having a Swedish localisation,
would give me nightmares! Naaeee!

Yeah, you are right, Ive never worked with class modules. I sure would like
to learn
but the articles I found in MSDN didnt give much of an idea to what
functions I should move to a class module and which advantages it would
bring.

OSumUpdate: Yes, this is probable the best solution. I chosen the simple way
of
letting the spreadsheet doing the calculation and repeatedly refresh the
labels to
to display the result. Lets say I change the quantity. The sales value for
the item,
the value for the order, the VAT and the total sum must be immediately
recalculated.
For that reason it was simple to collect the refreshing of these labels in
one procedure and let any change in every Text- and ComboBoxes.AfterUpdate
jump to OSumUpdate and the refreshing is done! What youre suggesting would
require much more code (there are 4 possible lines for registration) but its
probably a more proper solution.

Best regards
Mats


"keepITcool" wrote:

Mats.

Afraid your "rant" is old news :(

MSForms and VBA are US centric, and have been since conception.
(and MSforms hasn't changed since 97)

Numeric or date input in textboxes has always been a hassle.
Working with controlsource doesn't make it any easier,
since it's linked to Formula rather than FormulaLocal property

And in this case it's only a decimal... not dates etc.

A few tips:

control source.
No need to Activate worksheet "system" if you specify the controlsource
as the EXTERNAL (fully qualified) address of the range.

e.g. Me.Textbox1.ControlSource =
wksSys.Range("a1").address(external:=true)

To keep your coding to a minimum you can opt to force
any "," to a "." with a simple routine:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Application.DecimalSeparator < "." Then
If KeyAscii = Asc(Application.DecimalSeparator) Then
KeyAscii = Asc(".")
End If
End If
End Sub

If you've never worked with class modules then you'll have to repeat
this event handler for all "number" textboxes, else you could set up 1
class for all the boxes.


The call to oSumUpdate is not needed.
The label's controlsource can be set to a cell with a sum formula.
...and if you make that formula output a formatted STRING
with the TEXT worksheetfunction. e.g. =TEXT(SUM(A1:A3),"#,##0.00")
the formatting is taken care of.


HTH.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam