Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts

    VBA Currency format issues when users around the world run it

    Hi All,

    I have a sheet that users form Europe, Hong Kong and America use and it appears that the currency symbol changes if the user is not in the UK although the column fixed is for GBP.

    I can see that the -409 seems to be some localisation issue but have no idea exactly what.

    J-K shold be in

    Columns("J:K").Select
    Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"

    Columns("L:M").Select
    Selection.NumberFormat = "_-[$$-409]* #,##0.00_ ;_-[$$-409]* -#,##0.00 ;_-[$$-409]* ""-""??_ ;_-@_ "

    Columns("N:O").Select
    Selection.NumberFormat = "_-[$HKD] * #,##0.00_-;-[$HKD] * #,##0.00_-;_-[$HKD] * ""-""??_-;_-@_-"

    Columns("P:Q").Select
    Selection.NumberFormat = "_ [$-413] * #,##0.00_ ;_ [$-413] * -#,##0.00_ ;_ [$-413] * ""-""??_ ;_ @_ "

    Columns("R:S").Select
    Selection.NumberFormat = "_-[$$-409]* #,##0.00_ ;_-[$$-409]* -#,##0.00 ;_-[$$-409]* ""-""??_ ;_-@_ "

    Columns("T:U").Select
    Selection.NumberFormat = "_-[$CHF] * #,##0.00_-;-[$CHF] * #,##0.00_-;_-[$CHF] * ""-""??_-;_-@_-"

    Columns("V:Y").Select
    Selection.NumberFormat = "_ [$-413] * #,##0.00_ ;_ [$-413] * -#,##0.00_ ;_ [$-413] * ""-""??_ ;_ @_ "

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    Have you tried this:

    Code:
    [J:K].NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
    zeddy

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Since your workbook is used internationally, it might be prudent to include the locale ID for UK
    Code:
    [j:k].NumberFormat = "_-[$-809]* ###0.00;_-[$-809]* -###0.00;_-[$-809]* ""-""??_ ;_-@_ "

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Maudibe

    In my post, I just put the into the format directly. I think it is easier to understand.
    Are you saying this won't work?

    zeddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •