Currency Sign Conversion

EE Pros,

I have a very nice WB that I have been using in the U.S.  Most recently, I have had the request to use it in Europe.  Here's what I'm interested in knowing;

1.) Is there a way to create a Macro that when "fired" will look through a WB or specific Worksheets and change the formatting (i.e. currency to another currency SIGN; so e.g. dollars to euros, or pounds or Yen)?

and

2.) For graphics with the need for the same currency sign change, can I use the "Linked to source" choice in the graphic to reflect the change in currency or will I have to do something more creative?

I do not need currency conversion.  I need currency formatting.

Thank you in advance for sharing any expertise in this area.

B.
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCost AccountantCommented:
If you set up a 'Style' for 'changingCurency' you can set all cells to this format which need to change.
Then when you want to change the currency format it can be done in modify styles or throught VBA.

If you step through the example code in the attached workbook it should demostrate what can be done:

    Sub test()


    ActiveWorkbook.Styles("CurrencyChanges").NumberFormat = "£#,##0.00"
    
    ActiveWorkbook.Styles("CurrencyChanges").NumberFormat = "[$$-409]#,##0.00"

    ActiveWorkbook.Styles("CurrencyChanges").NumberFormat = "€#,##0.00"

    End Sub

Open in new window

U--Example.xlsm
Roy CoxGroup Finance ManagerCommented:
I have a method that works very well allowing the user to choose a sign and then changes all the currency cells. Unfortunately it's at home but I'll post an xample this evening if it will help
Bright01Author Commented:
Steve and Roy,

Thanks for responding.  Roy, I'll take a look at your approach when you send it tonight.  In the meantime, Steve, I'll review your links/comments.

Thank you,

B.
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Roy CoxGroup Finance ManagerCommented:
I'll knock up an example, it's actually part of a project of mine.
Bright01Author Commented:
Perfect! Thank you.  This should be interesting.........

B.
Bright01Author Commented:
Steve,

I tested your approach.... interesting use of "Styles"....... How do you automate that so you choose a currency and it populates all Currency cells in a WB?  

B.
Roy CoxGroup Finance ManagerCommented:
Here's the example that I promised. It uses named Ranges and Conditional Formatting, no VBA

1. Create a List of required currency names and call that List Currency
2. Use that list to create a drop down to select the required currency. I have used E1
3. Create Conditional Formatting formulas for each currency:-
  =Currency="Dollar"
 =Currency="GBP-Pound"
 =Currency="Euro"
4. Use the Format painter to copy this Conditional Format to all cells that you want to be changeable.

This will change all cells on all sheets that have this Conditional Format when the drop down is changed

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveCost AccountantCommented:
Bright, how you Automate depands on how you want to work.
The first thing to do is to set up all the changing currency cells to the style of currency.
When setting up the style be sure to set it to only change the Number format, by inticking the other boxes.
Once the changing currency cells have been set up it is then a choice between VBA to change the style or the user can change the style themselves.

If you want the user to change the style in the 'modify style' dialog:
To modify a style just right click it in the ribon and click modify, then click modify and change the format as required.

If you want to use VBA and link the format to a cell there are a few options for this:
You could list options (using data validation) and then change the format based upon the list.
Or you could have a cell with the number format in it such as $0.00 and then change the format to the cell contents.

The choice is really up to you and how you see the users of your file working.
It may also be limited by wether you want VBA in your file too.

Do you have a preference?
Bright01Author Commented:
Roy and Steve,

Thank you both for jumping in here.  I really like the easy conditional formatting idea from Roy because it seems simple and I can have a drop down for the Currency selection and avoid Macros and some complexity.  I think you would agree Steve, that this might be an easy way to accomplish the objective.  However, to Steve's point, if this is in a VB Module, then all cells formatted for "currency" would automatically change and I would avoid the Pasting formatting I'll need to do.  Is that right?

So here is where I am.  I've tried to work Roy's conditional formatting idea and I'm missing something in his directions.  I've attached my current "attempt".  Can you guys look at this and see what I'm doing wrong?  I've created a List in E1, and tried to set up 3 Conditional Formatted styles.  Copied each to the Test Cells in 1 and 2.  And I'm missing something basic.

Thank you, again,

B.
Currency-Formatting.xlsm
Roy CoxGroup Finance ManagerCommented:
One downfall with using a macro is that the user must be made aware that macros need to be enabled, This is not always easy.

Here;s your workbook set up, sorry my example didn't upload.
Currency-Formatting.xlsm
SteveCost AccountantCommented:
I would tend to avoid Macro in a situation like this.
Either allowing the user to change the currency with styles or with conditional formatting.
Styles are OK if there are users of many different currencies (or you cannot predict the format requirements of the user).
Conditional formatting is good if thare are not too many known currencies (max 5 imo) as users find it simpler.
All down to preferences and what is being done.
Bright01Author Commented:
Roy, this looks great!  And Steve, thanks for the validation!

Roy, what did you do to my WS to make it work?  I just need to know as I now transfer the knowledge to my production WB.

Thank you both.

B.
Roy CoxGroup Finance ManagerCommented:
I added the correct named ranges - see Formulas Tab -> Name manager

Then added the Conditional Formatting formulas.

I'll do a more comprehensive instruction when I gat time this weekend, but post back with specific questions

I use this in a large accounts workbook with no problems.
Bright01Author Commented:
Roy and Steve,

For embedded graphics within the WB, can I "link data to source" select and will it incorporate the new selection of currency in the graphic?

B.
Roy CoxGroup Finance ManagerCommented:
I'm not sure what you mean. Why not try what you want to d on the example workbook
Bright01Author Commented:
Doing that now....thanks!
Bright01Author Commented:
Roy and Steve,

This Conditional Formatting solution works great and is simple and elegant.  Thank you both for helping me out with this.  The last question has to do with graphics that are embedded in the Workbook.  The currency for those graphics typically are changed by accessing the formatting in the graphic and then changing currency.  What I was wondering is, there is an option to "link to source" the data in particular charts and graphs.  If I do that, will the formatting automatically change?

I know this question is above and beyond the question you have already answered so I'm going to close out the question and hopefully, if you already know the answer, you may simply post an additional comment.

Great work again.... and thanks!

B.
Roy CoxGroup Finance ManagerCommented:
Can you attach a example of what ou are using with the graphics
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.