Multiple currency format from UserForm

[Eduard]

Hi,

I have a UserForm with a ComboBox for the currency (30-35 items) and a TextBox for the amount. How do I paste the result of TextBox in a selected currency format in a cell in worksheet?

Thanks,
Eduard

[Kenneth Hobs]

If ComboBox value is say "$":
With WorkSheets("Sheet1").Range("A1")
.Value = TextBox1.Value
.NumberFormat = ComboBox1.Value
End With

[Eduard]

Thanks for prompt reply Kenneth. The macro crashes at this line:
.NumberFormat = ComboBox1.Value

Could it be because I have currency codes in the ComboBox (like "USD", "EUR", "GBP" etc.)?

Eduard

[Bob Phillips]

Yes, you will need a lookup table of currency codes against the currency codes as defined here (http://office.microsoft.com/en-gb/excel-help/creating-international-number-formats-HA001034635.aspx), and format accordingly.

[Eduard]

I'm sure there should be another way. In Excel 2010 you can format a cell using either the currency symbol ($, €, £ etc.) or the code (USD, EUR, GBP etc.)

[Kenneth Hobs]

As XLD said, you need a lookup table which can be an array sort of table or other method to add your format string. for some cases, you can do it like this:

e.g.
With WorkSheets("Sheet1").Range("A1")
.Value = TextBox1.Value
'USD
.NumberFormat = "[$" & ComboBox1.Value
& "] #,##0.00"
End With

Record a macro and change to your formats to see how the syntax would go for each.

[Eduard]

Hi,

Here is the code I found which works fine in Excel 2010 without lookuptable. Don't know if it will work in previous versions of Excel.


With WorkSheets("Sheet1").Range("A1")
.Value = TextBox1.Value
.NumberFormat = "#,##0.00 " & "[$" & ComboBox1 & "]"
End With


Thanks for your efforts, gentlmen.

Eduard

[Bob Phillips]

But that only handles one currency, what about the 30+ others?

[snb]

Private Sub Combobox1_change()
With WorkSheets("Sheet1").Range("A1")

.Value = TextBox1.Value
.NumberFormat = "#,##0.00 [$" & ComboBox1.Value & "]"
End With
End Sub


It handles as many currencies the combobox contains.

[Eduard]

I agree with snb.