Decimal numbers problem

[bsk]

Hello, Dears,
Our decimal number separator is comma. I have a problem with decimal problems in the userform. I can get decimal data in the textbox, but It doesn't perform multiplication with the decimal data. For example real data is 0,41 however excel see it like 41. The codes are below, I kindly ask some help.
Private Sub ComboBox1_Change()
Dim tablo1 As Range
Dim A As Integer
Dim B As Double

Set tablo1 = Worksheets("sayfa1").Range("A2:A22")
A = ComboBox1.Value

B = Application.WorksheetFunction.VLookup(A, tablo1.Resize(, 2), 2, False)
TextBox1.Value = B
B = CDbl(TextBox1.Value)
End Sub

Private Sub CommandButton1_Click()
Dim adet As Integer
Dim B As Double, Boy As Double, toplam As Double

B = CDbl(TextBox1.Value)
adet = CInt(ComboBox2.Value)
Boy = CDbl(TextBox2.Value)
toplam = B * adet * Boy
TextBox3.Value = toplam
End Sub

 

[Micron]

Maybe this thread will help you

decimals separator problem

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...

www.mrexcel.com

If you're grabbing code from elsewhere, make sure it doesn't mess up textboxes with text only that contains commas, such as this sentence.

 

[PeteWright]

I'm not sure, but would including this in your code work?

Application.DecimalSeparator = ","
Application.UseSystemSeparators = False

 

[bsk]

Thank you for the replies. I tried all the codes you sent me but I couldn't solve it. Attached a link for my example file. May be, it helps to solve .

Lifebox Transfer: Upload File! Send and Share Large Files

Lifeboxtransfer is a fast file transfer service for sharing your files easily without membership. Click and start sharing your files fast and easily with lifeboxtransfer.

lifeboxtransfer.com

 

[Alex Blakenburg]

Give this a try. I haven't done this before so feel free to try and break it.
If we were happy to assume that no user would untick the "Use system separators" then we could have gotten away without the function.

Note: Your B figure is stored in the sheet to 3 decimal places but you are only displaying 2 in column B and then only displaying 2 in the Form. This is has the effect of rounding B to 2 decimals and using that rounded number in your calculations.

Private Sub ComboBox1_Change()
    Dim tablo1 As Range
    Dim A As Integer
    Dim sB As String

    Set tablo1 = Worksheets("sayfa1").Range("A2:A22")
    A = ComboBox1.Value
    With Application
        sB = .Index(tablo1.Offset(, 1), .Match(A, tablo1, 0), 0).Text       ' Get value as formatted
        TextBox1.Value = sB                                                 ' Use current formatting
    End With
 
End Sub

Private Sub CommandButton1_Click()
    Dim adet As Integer
    Dim B As Double, Boy As Double, toplam As Double

    B = CDbl(LocalToUS(TextBox1.Value))
    adet = CInt(LocalToUS(ComboBox2.Value))
    Boy = CDbl(LocalToUS(TextBox2.Value))
    toplam = B * adet * Boy
    TextBox3.Value = USToLocal(Format(toplam, "0.00"))
End Sub

Function LocalToUS(ByVal sValue As String) As String
    Dim sysDecimalSeparator As String
    sysDecimalSeparator = Format(0, ".")
    With Application
        If .UseSystemSeparators = False _
            And .DecimalSeparator <> Format(0, ".") Then
                sValue = Replace(sValue, .ThousandsSeparator, "")                   ' Remove thousands separator
                sValue = Replace(sValue, .DecimalSeparator, sysDecimalSeparator)    ' Convert Decimal separator to Windows separator
        End If
    End With
    LocalToUS = sValue
End Function

Function USToLocal(ByVal sValue As String) As String
    Dim sysDecimalSeparator As String
    Dim sysThousandSeparator As String
    sysDecimalSeparator = Format(0, ".")
    sysThousandSeparator = Mid(Format(1000#, "#,#"), 2, 1)
 
    With Application
        If .UseSystemSeparators = False _
            And (.DecimalSeparator <> sysDecimalSeparator _
            Or .ThousandsSeparator <> sysThousandSeparator) Then
                sValue = Replace(sValue, sysThousandSeparator, "@")                ' Replace Thousands separator with place holder
                sValue = Replace(sValue, sysDecimalSeparator, .DecimalSeparator)   ' Convert Decimal separator to Application separator
                sValue = Replace(sValue, "@", .ThousandsSeparator)   '             ' Convert place holder Application Thousands separator
        End If
    End With
    USToLocal = sValue
End Function

 

[bsk]

Thank you for the answer. I've solved it.

 

[Alex Blakenburg]

Thanks for the update. If you did not use any of the options provided please post your solution and you can mark it as the solution. It may help others in the future.

 

[bsk]

As I said I'd solved it and the code is below.
Dim adet As Integer
Dim B As Double, Boy As Double, toplam As Double

B = CDbl(TextBox1.Value)
adet = CInt(ComboBox2.Value)
Boy = CDbl(TextBox2.Value)

toplam = Replace(B, ".", ",") * adet * Boy
TextBox3.Value = toplam
End Sub

 

[Alex Blakenburg]

My guess is that in the Box B you are still showing the "period" as the decimal delimiter.
In "Private Sub ComboBox1_Change()" consider using:
TextBox1.Value = Format(B, "0.00")

 

[Alex Blakenburg]

I have just had another look and since column B is only displaying 2 decimals but has figures to 3 decimals, using Cstr is a better option than format ie
TextBox1.Value = CStr(B)