Change currency symbol when converting currency

[mgd470]

Hello,

I have a worksheet where I have several currency options, and all the values are converted based on the currency selected. I have also added the a macro to format the cells using the symbol for the currency selected. The code I am using is below:
Currency_Symbol = Range("G2").Value
Range("D23:M23").NumberFormat = Currency_Symbol & " #,##0.0000"

In cell G2, I have the following formula:
if(C2="USD",char(36),if(C2="GBP",char(163),char(36))) <- Simplified here to only have dollars, pounds, and euros

This works perfectly, and formats the cells correctly based on the currency selected.

My problem is that this worksheet is used by users all over the world. When my British user uses the worksheet, if she selects USD, it still displays the currency symbol as pounds. If she sends it to me, it works fine with either currency, so I cannot replicate her issue using my version of Excel. I assume that this is some sort of formatting issue, but am not sure.

Does anyone have any suggestions for how to make this work on my colleague's British version of Excel?

Thanks for your help!
Mark

 

[Beyond_avarice]

Let's start by reducing your G2 formula.

=IF(C2="USD",CHAR(36),CHAR(163))

Your script is working for me (US Excel). You might need some of the UK members in here to comment, but I wonder if there is an issue coming from the format you present as "#,##0.00". I've had to work in Euro format before and would need to change my "System Separators" to Decimal = ","(comma) and Thousands= "."(Period).

 

[mgd470]

Thanks! I have confirmed that the formatting works fine with the commas instead of decimals. It's only the Currency Symbol that isn't working properly.

 

[Beyond_avarice]

Your original G2 formula will always result in USD $ unless "GBP" is entered in C2. Is C2 a drop down list or is this cell populated by the user?

 

[Greg Truby]

Were I tasked to skin this cat I'd name cell G2 CurrencyIndicator and then add some conditional formatting rules that set number format based the value of CurrencyIndicator, one rule for each currency you wish to offer. That would eliminate the need for using any VBA at all. Then I could apply the CF rule to whatever range(s) I needed.

 

[mgd470]

That is correct. C2 is a drop down menu, where the user selects the currency. For this discussion, I simplified it a bit, since the only issue is when my British user selects USD, it still displays in GBP. The actual dropdown has about 10 different currencies that can be used. If the British user selects any currency other than USD, it works correctly.

 

[mgd470]

That's a good point, Greg. I probably could do this with just conditional formatting (which is essentially what I'm using the Macro to do), but there are several other tasks that the macro is also doing which made this a pretty easy approach. In works fine in all cases except when the British user selects USD.

 

[Greg Truby]

Looking at yer formula there it appears that yer letting the user pick "USD" or "GBP" or whatever; and then returning a "$" or a "£" symbol. If ya wanna stick with the VBA route, then I'd do one of two things to make this feller a lot more scaleable, debuggable, maintainable, etc. I'd nix the IF() approach, drop my abbreviations and symbols into a table and just use an INDEX(MATCH()) approach (or VLOOKUP() if'n ya gotta a prejudice against INDEX & MATCH). Either that or I'd move the logic into the macro using a SELECT CASE structure (IMHO the harder-to-maintain approach). Using a table, then should ya decide to add another currency option (like ¥ or €), it ain't much harder than fallin' off a stump. And it oughtta make debuggin' a lil' more straightforward.