PDA

View Full Version : Number format



Lberteh
08-19-2013, 09:04 AM
Hi all,

I have a userform named Telas which contains a combobox getting its items from a range of cells in my worksheet.
Here is the code:


Private Sub UserForm_activate()

Dim WS As Worksheet
Dim LastRow As Long
Dim aCell As Range

Set WS = Worksheets("Listas")

With WS
LastRow = .Cells(.Rows.Count, "K").End(xlUp).Row

For Each aCell In .Range("K2:K" & LastRow)
If aCell.Value <> "" Then
Me.ComboBox1.AddItem aCell.Value
End If
Next
End With

With WS
LastRow = .Cells(.Rows.Count, "L").End(xlUp).Row

For Each aCell In .Range("L2:L" & LastRow)
If aCell.Value <> "" Then
Me.ComboBox2.AddItem aCell.Value
End If
Next
End With


End Sub

The problem is: In my cell it is a number: 14.7
When click the combobox, the number in the list is 14,7 and because of this "," I can't multiply this number cause it only identifies as 14.
How can I make it get the correct value without substituting "," for "." with code?

Thank you

Kenneth Hobs
08-19-2013, 09:21 AM
Use Replace().

Lberteh
08-19-2013, 09:34 AM
Hi Kenneth,

thanks for your reply.

I tried this


Private Sub ComboBox2_Change()
Me.ComboBox2 = Val(Replace(Me.ComboBox2, ",", "."))
Label7.Caption = Val(Me.ComboBox1) * Val(Me.ComboBox2)
End Sub

but nothing happened.

any thoughts?

Kenneth Hobs
08-19-2013, 09:55 AM
This works for me. Maybe you need to check on the international decimal settings. A comma in USA means a thousand separator so replace comma with period would be wrong. Commas should be replaced with nothing in that case. Same with dollars like $. Replace with nothing, "".

Label7.Caption = Replace(ComboBox1.Value, ",", ".") * Replace(ComboBox2.Value, ",", ".")