+ Reply to Thread
Results 1 to 3 of 3

Cells formatted as currency changing to custom on reopen when user changes currency symbol

  1. #1
    Forum Contributor
    Join Date
    11-15-2008
    Location
    Arizona
    MS-Off Ver
    Excel 2013
    Posts
    179

    Cells formatted as currency changing to custom on reopen when user changes currency symbol

    I have designed a spreadsheet that people in other countries will be using. All the currency cells are formatted as currency, so for me the $ symbol shows. When I go into my control panel and change the default currency to Euros or something, then all the cells formatted as currency change to the Euro symbol, which is perfect. However, when I close out of the workbook and then reopen it, it no longer shows the Euro symbol. It shows the American dollar because all of the cells formatted as currency got switched to custom ("$"#,##0.00), so it doesn't read the computer's default currency setting and instead forces the $ symbol since it is a custom setting.

    When I change my control panel back to the American dollar, the cells are once more formatted as currency, not that custom setting.

    I had a gal in another country test it, and same thing. She can't get the pound symbol at all. Since her computer settings are already configured to the pound symbol, when she opens the workbook for the first time, all of the currency cells are custom cells forcing the $.

    This is a problem since my workbook will be used from people in a variety of countries. (No need to do any conversions as the user will be entering in the amount. I just need the symbol to change).

    How do I get these cells to STAY as currency ones, so it reads the computer's default currency.

    Or is there some VBA where a user can enter in their symbol and every single cell marked as custom will change to that symbol? Since their workbooks are showing custom for any cell I had made currency. OR some VBA, that will make it so they don't switch to custom upon opening.
    Last edited by dsrt16; 10-10-2018 at 05:33 PM.

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: Cells formatted as currency changing to custom on reopen when user changes currency sy

    That is very annoying. I just tried it, using variations on the locale codes which can be used with dates (to give d/m/y or m/d/y for example) but although Excel accepted the number format, the result was the same as you describe.
    If Excel number formats accepted a cell reference, then you could have a cell where the user enters his/her currency (or a drop-down of possible currencies) and have that used in a custom number format. But you can't use a cell reference in a number format.

    I'm sorry to say that the only solution I can think of is to have the cells just formatted as numbers with decimals and have extra cells adjacent to them with the currency symbol in, linked to a cell where the user enters the required symbol (or a drop-down). Definitely not ideal, but it's all I can suggest, sorry.

    (There may be a VBA solution, but if so it's way beyond my skills, sorry.)
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Forum Contributor
    Join Date
    11-15-2008
    Location
    Arizona
    MS-Off Ver
    Excel 2013
    Posts
    179

    Re: Cells formatted as currency changing to custom on reopen when user changes currency sy

    Thanks. Since this may need a VBA solution, I am going to post about it in the VBA forum instead.

    VBA post here.
    Last edited by dsrt16; 09-26-2018 at 03:29 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] User Form Textbox Formatted as Currency
    By billgyrotech in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-05-2017, 02:32 PM
  2. Changing currency based on user input
    By snowman123 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 11-06-2015, 01:07 AM
  3. Changing the symbol for all currency formatted values in a spreadsheet - how?
    By Jason66 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-18-2013, 11:01 AM
  4. Currency conversion insert currency symbol
    By siobeh in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-01-2013, 05:50 AM
  5. Change currency symbol of multiple cells depending on input into another cell
    By shaunnehughes in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-10-2010, 03:27 AM
  6. Replies: 2
    Last Post: 09-23-2009, 03:03 PM
  7. Replies: 4
    Last Post: 07-07-2006, 04:20 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1