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?!

 

[Mat]

Well, I wrote a function for it!

At first, I thought it was really complicated to modify my module since I used some loop like:

    For Each ctrl In frmOptions.Controls
         If TypeName(ctrl) = "TextBox" Then
            On Error Resume Next
              ctrl.Text = Range(ctrl.Name).Value
            On Error GoTo 0
         End If
    Next ctrl

I have named some range in excel with the same name for the textboxe so it was easier to copy the value from the range to the textboxes with that function. the error resume next is for the textboxes that arent linked with any range...

using the substitute function that andrew gave was great, but the problem is that some textboxes may be text value, not numbers, so if these textboxes contain comma's or dot, I don't want them to be changed with the default decimal separator...

And It was difficult to split the loop for control with Text, and Ctrl with number...

I decided to force the user to use the dot as decimal separator... it was working well, but the user needed to close excel and re-open to apply change about the decimal separator.

But there, thinking about it... I wrote a function to be sure when the value in the textbox is a number then when pasting it's considered as a number, not a text and keeps the specific format number ("Greater than "0.00). And if it was text, then don't do any change to the value.

Is here my function I wrote:

Public Function DSCh(sValue As Variant) As Variant
Dim DS

DS = Application.International(xlDecimalSeparator)
sValue = CStr(sValue)

DSCh = WorksheetFunction.Substitute(sValue, ",", DS)
DSCh = WorksheetFunction.Substitute(DSCh, ".", DS)

On Error Resume Next
DSCh = CDbl(DSCh)

If Err.Number <> 0 Then DSCh = sValue

End Function

So now I just changed the line :

'to paste in the userform's textboxes
ctrl.Text = Range(ctrl.Name).Value By
ctrl.Text = DSCh(Range(ctrl.Name).Value)

And

'to paste in the excel's range
Range(ctrl.Name).Value = ctrl.Text
Range(ctrl.Name).Value = DSCh(ctrl.Text)


It seems to work pretty well...
In my case it doesn't handle thousand separator
maybe adding

If DS <> "." Then
Code
Else
DSCh = sValue

so it will change the value only if the decimal separator is something else than the dot (so probably the comma's, and in that case the thousand separator isn't the comma, so it's ok)

there is surely a way of handling thousand separator correctly, but in my case I didn't need it so...