Change currency symbol when converting currency

mgd470

New Member
Joined
Sep 7, 2017
Messages
4
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Let's start by reducing your G2 formula.

Code:
=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).
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top