1

I have a workbook that is distributed to users located in different countries of the world. On this workbook, I have a drop down where the user can select the country and currency e.g Japan (USD), Japan (JPY), China (USD) and so on. Based on this input, a macro formats some ranges with the currency symbol specified in the drop down e.g $ for USD and ¥ for JPY. The code that I'm using is:

 With Worksheets("MYSHEET").Range("ABC1to5_CURRENCY")
 .NumberFormat = "$ #,##0;[Red]$#,##0"
 End With

Now the issue is that this code changes when run on a computer with different language settings. For instance, it shows ¥ instead of $ on Japanese version of Windows OS. Also the code ALT+0165 does not yield the same result of YEN symbol in the Japanese version. Can anyone please tell me how to hardcode these currency symbols so that they display the same across all computers?

Thanks!

1 Answer 1

0

Try if the following helps you: In an empty or test workbook put 42 into a cell and format it as Currency $ (United States). Open the "Format Cell" dialogue and switch it to be "User defined" It should say something like this:

_-[$$-409]* #.##0,00_ ;_-[$$-409]* -#.##0,00 ;_-[$$-409]* "-"??_ ;_-@_ 

Maybe you can get to the .NumberFormat you need this way...it looks to me like it defines locales with the "409" in that string.

good luck! ;-)

5
  • I want to get rid of this 409. Because it changes with locale. Also, on the Japanese version the code [$¥-411]#,##0.00 appears as [$・-411]#,##0.00 whatever this ・ means is beyond me :( On the other hand [$$-409] #,##0;[Red]$#,##0 shows ¥.
    – Muneeb M
    Jun 13, 2013 at 7:47
  • fascinating. does Alt+190 give you a ¥ on the japanese version? Jun 13, 2013 at 8:28
  • It does. But it gives ╛ on the English version. You see, I'm trying to display currency symbols independent of the locale. When a user selects Japan (JPY), show ¥ on every computer irrespective of the language. Is it possible to do this?
    – Muneeb M
    Jun 13, 2013 at 8:40
  • wow. it does give me a ¥ for the 190 on a german machine (german MUI) but you are right on the english machines it gives me that thing you got as well. can you detect the locale and treat the cases differently in your code? not a nice solution though. Jun 13, 2013 at 9:25
  • Nope, not gonna cut it. Anyway, thanks for your help man.
    – Muneeb M
    Jun 13, 2013 at 13:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .