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

    VBA for currency symbols used on a single sheet in different locations

    Hi all,

    I have a spreadsheet which is used by people all over the world for my department. There are some issues with the currency symbol that is displayed depending if you are say in Hong Kong to the UK. Each column if a fixed currency. i.e. column J & K are , and L & M are $. My VBA code is below:

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

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

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

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

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

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

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

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


    Do you have any ideas how this could be fised to allow the currency to stay regardless of where the file is being opened.

    Kind regards,

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    A dropdown asking them to select the currency?

  3. #3
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    They dont need to select it, no matter where the sheet is opened up in the world i always want cells J:K to have the pund symbol and L:M to have the USD symbol. Currently the sheet changes the symbol if the user is based in HK. i.e if in HK the $ symbol changes.

  4. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Put the appropriate code in the ThisWorkbook module using worlbook_open

  5. #5
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts
    I'm sorry, i dont understand, there is a macro that does lots of stuff and this is just the peice of the code for the currency symbol.

  6. #6
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    You didn't post a file or tell us which sheet in the file. I suggested that you put in the ThisWorkbook module and adjust for any line wrap and your desired sheet.

    Private Sub Workbook_Open()
    With Sheets("sheet1")
    .Columns("J:K").NumberFormat = "_-[$] * #,##0.00_-;-[$] * #,##0.00_-;_-[$] * ""-""??_-;_-@_-"
    .Columns("L:M").NumberFormat = "_-[$$]* #,##0.00_ ;_-[$$]* -#,##0.00 ;_-[$$]* ""-""??_ ;_-@_ "
    End With
    End Sub

Posting Permissions

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