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
    3,233
    Thanks
    154
    Thanked 609 Times in 579 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,846
    Thanks
    146
    Thanked 723 Times in 657 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
    3,233
    Thanks
    154
    Thanked 609 Times in 579 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
  •