decimals separator problem

[Mat]

Hi,

I have a problem, I know the decimal separator is the source, but I don't understand why...

Here is the situation :

I have a form with textboxes and a command button...
User fill the textboxes with the value they want, once it is done, press the ok button (commandbutton) and all the value are put in different cell..

As simple as This :

Private Sub OkButton_OnClick()
Range("A1").Value = TextBox1.Value
End Sub

Ok now, my decimal separator is the ","
In the textboxes, the user may enter both 1.1 or 1,1
The result in Range ("A1") will be 1,1 at screen. (in both case)
and in B1 I could without problem do : =A1*2, and it will give me 2,2

THe real problem is there :
My cell A1 is formatted with a personnalised format number :

"> "0

Now if I enter manually 1,1 into cell A1, I see :

> 1,1

Just as expected...
but if I use the Form, I get in the cell 1,1 without the "Greater Than" symbol, just as if it was text in the cell, but I still could use the value in other formula... if I want to get back the "Greater Than" symbol, i must select A1, click in the Formula bar, and press enter (without any change) to make it appear...

if I use the "." as decimal separator, I have no problem...

...

What happens?!

 

[parry]

Interesting because I get different results if I enter directly into a cell that has a custom format of "> "0. My number settings are "." decimal separator and "," thousands separator.

1,1 results in 1,1
1.1 results in > 1
1,372 results in > 1,372

The format "> "0 is basically making it an integer but if you use a comma as the thousands separator and the figure is less than 4 digits your going to run into problems.

 

[Mat]

Well, any solution?

Maybe I should check when the workbook is opened if the decimal separator is the comma, if yes, then change it for the dot, and when I close, put it back to the comma...

But.. it must be usable with almost all windows platform... win98, 2000, xp, Nt... And to do so in vba, I guess the user must have the right to modify the regional parameters.... I'll search for this, I already saw some code to change the regional parameters, but I don't know if it was usable with all windows OS...

 

[Andrew Poulsom]

With a comma as the decimal separator you can enter either 1,1 or 1.1 (dot from number pad) in a cell and it treated as 1 point 1. But VBA is not so clever. Try this:

Private Sub CommandButton1_Click()
    Dim DecSep As String
    Dim ThouSep As String
    DecSep = Application.International(xlDecimalSeparator)
    ThouSep = Application.International(xlThousandsSeparator)
    If DecSep = "." Then
        Range("A1").Value = TextBox1.Value
    Else
        Range("A1").Value = WorksheetFunction.Substitute(TextBox1.Value, DecSep, ThouSep)
    End If
End Sub

 

[parry]

Im not an expert at this but Ive noticed a few things when testing options. As you may know if you enter text into a cell then the value is left aligned in the cell, while numbers are right aligned.

If I enter 1,1 in a cell with your format it is left aligned indicating text while if I enter 1.1 its right aligned indicating a number. So the obvious answer is not to include the comma when the value is entered into the cell if you want to retain a number format.

Perhaps you could explain what 1,1 is supposed to mean? Is this is an actual number value (ie equiv of 1.1) or is it supposed to be textual. As I said I couldnt replicate entering 1,1 into a cell and get > 1,1 like you originally posted. Why enter the comma?

Personally I would either prevent the comma being entered at input or convert the comma to a decimal or perhaps remove the ciustom format and format the value when passing from textbox to the cell. Without knowing what your trying to achieve its difficult to say what option to take.

If your insistent on changing the thousands seperator then try this but I wouldnt recommend it as your changing a global setting.
1) change the thousands seperator to a different character (I chose ~)
2) enter custom format as "> "0,0
3) user to enter values without the comma. ie 11 results in > 1,1.

 

[Mat]

Hi, using code given by Andrew, I've come to an end that seems to be almost Ok.

Parry:

My thousand separator is the Space, and decimal separator is the comma. So when I enter 1,1 directly in the cell (or any other number with decimal since it's a number, not a text) I get "Greater Than 1,1" as a number (right-aligned) But When I use a textbox in a userform, even if I enter 1,1 or 1.1, when I do:
Range("A1").Value = Textbox1.Value then I get 1,1 in the Range A1 but now it is seen as a Text! Not a number, even If the decimal separtor is the good one.

So using some code given by Andrew (Thank you)

DecSep = Application.International(xlDecimalSeparator)
Range("A1").Value = WorksheetFunction.Substitute(Textbox1.Value, ",", DecSep)

Without the If..(since if the decimal separator is the dot, then the substitute function won't change the value) When the user enter 1.1 in the textbox, it print "Greater Than 1,1" in the cell, considered as a number.

And even if the "," is the decimal separator, when the user enter number in a textbox using the "." of the numpad, then it's a dot, not a comma. And it works well, but if the user use the comma at the right of the "m" then it doesn't work... but as far as i know, there is not a lot of people who doesn't use the dot of the numpad...

 

[parry]

Hi Mat try this. The VAL method turns a string into a number. I used the ASCII # instead of a comma but you could use a comma just as well.

Private Sub CommandButton1_Click()
Dim DecSep
DecSep = Application.International(xlDecimalSeparator)
Range("A1").Value = Val(WorksheetFunction.Substitute(TextBox1.Value, Chr(44), DecSep))
End Sub

hth
EDIT oops you dont need the testing vaiable so I deleted that. I was trying a few things :wink:

 

[parry]

Hi again, you misght find this page useful which explains some common traps in VBA and applies equally to Access and Excel. I think your situation probably loosely fits in the Automatic Typecasting problem where VBA is turning it into a string because of the prescence of the comma. Im only guessing but that seems correct considering your problem.

regards
parry.

 

[Mat]

Thanks for your link Parry, and btw, I just saw that I made a mistake in the format number I gave above..

it's "> "0.00 or "> "0,00 the real one, not only "> "0...

 

[parry]

No worries, I think we are both learning alot about Excel. I didnt even know about commas and what they would do in a textbox until this thread so at least I have an idea if the problem strikes.