Changing Currency

EE Pros,

I have attached a simple spreadsheet as an example of what I'm trying to do.  I want to be able to select a currency (dollars, yen, yuan, euro, etc.) from a drop down list and have a global macro (for the workbook) change the format for the current currency (dollars) to the selected currency.  Here's the reason/logic; I have people who use my spreadsheet around the world and they are constantly asking, can I get it in my currency?  So you will see in the sample I have attached.... There is an input field and a formula field and the formula is based on a % change (that requires no change in currency).  So how do I automatically change the currency format to reflect the selected currency?

That's it!

Thank you in advance.

B.
Change-Currency.xlsm
Bright01Asked:
Who is Participating?
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.

Zack BarresseCEOCommented:
Hi there,

Interesting problem. I'm assuming you're trying to correct for currency conversion with your percent change column? As I'm sure you're well aware, you can't just change a format of a value as not all currencies are equal.

That being said, there is no built-in functionality for this. You could setup a change event to look at the cell which contains your drop down. To do so, follow these steps...

Hit CTRL + F3 to open the Name Manager (also accessible from the FORMULAS ribbon tab)
Click New, define name as SelectedCurrency, refers to =Currency!$E$4
Click New, define name as CurrencyList, refers to =Currency!$B$4:$B$12
Click New, define name as UpdateCurrency, refers to =Currency!$J$5:$J$7
Click the Close button

Assuming those names are defined, you can use this code in the worksheet module...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rFind As Range
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address = Me.Range("SelectedCurrency").Address Then
        Set rFind = Me.Range("CurrencyList").Find(What:=Target.Value, LookAt:=xlWhole)
        Me.Range("UpdateCurrency").NumberFormat = rFind.Offset(0, 1).NumberFormat
    End If
End Sub

Open in new window


To install this code, right click the sheet tab and select View Code, paste in there. It will run when you change the 'Selected Currency'. Then whatever format you have in the cells in C4:C12 will be applied to whatever value is found in the drop down in E4.

HTH

Regards,
Zack Barresse
0
Bright01Author Commented:
Zack,

Thanks for the quick reply.  Actually, I'm not compensating for the change in currency in the % column.  It's simpler then that.  I have a set of formulas (represented by the % column), that run a single currency factor through and on the result end, produce a value in the same currency (as a result of input currency * % = same currency result).  My issue is that I need to be able to represent all of the currency fields in the Workbook, in the same currency.  So, there is no conversion....it's simply a matter of formatting.

So.... on the initial tab, what I will have is a drop down that asks "which Currency are we modeling in?" and if the pick is Euros, then all of the currency fields will reflect Euros.  Put a Euro in the input field, and out comes a Euro result.

Make sense?

B.
0
Zack BarresseCEOCommented:
Yup. Follow the instructions in my post and that is what you shall have. :)

Zack
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Bright01Author Commented:
Great!  So in the Range Names, I need to list all, or each of the fields where the currency formatting will change?  I have multiple tabs (worksheets) where this will take place. (i.e. "Update Currency!)?

B.
0
Zack BarresseCEOCommented:
Well the code was based on your sample file. I didn't know you had additional requirements. ;) It helps if you splay them out right from the get-go. :)

Can you define a little more what you have? Is it one cell where you choose the format, e.g. Dollar, etc? Is it only one location where you have the cells pre-formatted? Is it multiple cells on multiple sheets where the values should update? If you can define the sheets and cell addresses of those we can get it working for you. :)

Zack
0
Bright01Author Commented:
Zack,

Thanks for the reply.  And my apologies for not spelling it all out.  Generally, if I can get the sample right and keep it simple, I can figure out (and learn) how to make it work in a larger context.  Let me see if I can explain this better;

I have a workbook that has an Input Tab where data is entered.  Data is either in currency (dollar) format, straight 2 digit numbers (non-currency) and % formats. Calculations are made on another tab. Finally, results are displayed on a third tab.  The results are, for example, some in currency, in the case of a NPV (Net Present Value ((e.g. $1,243))), and some are in 2 decimal format, in the case of BEP (Breakeven Point ((e.g. 6.34))), and finally, some are in a % format such as in the case of an ROI (e.g. 234%))).  In the case where Currency is involved, I'd like to be able to change the Currency format to the appropriate Currency "type".  So if the user is in Germany, he/she could select (drop down), Euros, and the currency formats in all the currency cells would change to Euros from Dollars.  There would be no conversion, since they haven't entered in any information and the issue isn't currency conversion, it's simply a formatting issue.  So at this point, I do not need to translate Currency, but simply allow a user to select a Currency type and then change the symbols in specific fields where Currency is used.  

My thought from getting the example understood, was to use either specific cell references in a table, specific range names or some form of search and replace where the format within the Worksheet or Workbook was $ Currency, replace with Selected Currency.

I hope that makes sense.

And thank you in advance for the help.

B.
0
Bright01Author Commented:
Zack,

Hope I didn't confuse you with the description.  Let me post an easier to understand model.

Again, this is a simple case of changing the formatting (Currency) on a very specific set of cells (now in a table); based on a user selection from a drop down menu.  My problem is I don't know how to build the macro.

Thank you again,

B.
Change-Formatting.xlsm
0
Zack BarresseCEOCommented:
Hi,

Sorry, I missed the last post you had, hence me not replying, apologies. And also sorry I wasn't clear. The solution I posted can be adapted to your file.  The only change is you are now defining the cells to change as text in a list of cells, which I will adapt in the below code.

For the above solution to work you would have had to name some ranges. In the below solution you don't have to name them, but you do have to define their ranges in the top of the code. I adapted the code to the workbook you posted.  Here is the code...

Option Explicit

Const SelectedCurrency As String = "D4"
Const CurrencyList As String = "B4:B12"
Const ChangeCellStart As String = "E4"

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rFind As Range, rStep As Range
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address = Me.Range(SelectedCurrency).Address Then
        Set rFind = Me.Range(CurrencyList).Find(What:=Target.Value, LookAt:=xlWhole)
        If Not rFind Is Nothing Then
            For Each rStep In Me.Range(Me.Range(ChangeCellStart), Me.Cells(Me.Rows.Count, Me.Range(ChangeCellStart).Column).End(xlUp))
                Me.Range(rStep.Value).NumberFormat = rFind.Offset(0, 1).NumberFormat
            Next rStep
        End If
    End If
End Sub

Open in new window


To install the code, right-click the Currency sheet tab and select View Code. Paste all of the code there.

For this to work it makes some assumptions. First that the defined cells (constant lines at top) are appropriate ranges. Also that your currency table is setup as-is in the sample file, with currency names on the left (which match your drop down "SelectedCurrency") and the cells on the (adjacent) right have the formats applied you want. Finally (this is a change from the solution in the post above) the cells are identified you want to change starting in E4. The code will look at that range dynamically from the bottom of the sheet going up, so there should be no data below that list in the entire column. It assumes there will always be data there.

This code is a change event, which looks to the SelectedCurrency value to change, so there is no need to assign it to a button. Do you want it on a button instead?

Zack
0
Bright01Author Commented:
Zack,

Thanks for the quick reply!  I don't need the button given the way you put the macro together...works fine.  I pasted the code in the Change formatting Spreadsheet (latest I sent you) and it works; EXCEPT; it changes the cell I4 but not K9.  I added to the table:

E4:E5, thinking it would match to E5 also.

Const SelectedCurrency As String = "D4"
Const CurrencyList As String = "B4:B12"
Const ChangeCellStart As String = "E4:E5"

What am I doing wrong?

B.
0
Zack BarresseCEOCommented:
If you leave the code as-is, the only thing you would need to change is on your worksheet, starting in E4 going down. Each cell should house a cell address that you want to change.  For example in your sample file in E4 you had "i4", and in E5 you have "k4". I took this to mean the target cells to update the formats on were I4 and K4. So if you wanted K9 to also be included in what was formatted you would type "K9" in cell E6.

Does that make sense? Is that what you want?

Zack
0
Bright01Author Commented:
Zack,

Right!  That works.  Almost there...... I put it in my production model and had one problem.  One of the fields that I need changed is on another worksheet within the workbook.  I thought I could simply reference it in the table (i.e. 'Customer-Input!C5) and it would change.  However I get an error on this line of your code;

                Me.Range(rStep.Value).NumberFormat = rFind.Offset(0, 1).NumberFormat

So I think it works perfectly for within the spreadsheet, but not referring to cells in other Worksheets within the workbook.

Is this a simple fix or should I ask another question?

Much thanks,

B.
0
Zack BarresseCEOCommented:
Ah, gotcha. The reason it fails is because we're referencing the worksheet from which it is called. We can do it any way you'd like. Would it be easier to have the worksheet referenced in an adjacent cell? Or would you want to reference it in those cells like you did? Just let me know your preference and we'll get it written up for ya. :)

Zack
0
Bright01Author Commented:
Zack,

So what I need is to be able to reference in the "table" any cell in the global Workbook.  That may mean making the macro reside in a module vs. a sheet.  Not sure... but again, its as simple as saying that, in the table, this reference cell, in this worksheet is the cell we are changing.

I hope that makes sense.

B.
0
Zack BarresseCEOCommented:
Gotcha. If you want to reference cells such as "K4", or "Sheet1!K9", then you can use the following code. It parses out the worksheet name based on the ! character...

Option Explicit

Const SelectedCurrency As String = "D4"
Const CurrencyList As String = "B4:B12"
Const ChangeCellStart As String = "E4"

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rFind As Range, rStep As Range
    Dim sSheet As String, sRange As String
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address = Me.Range(SelectedCurrency).Address Then
        Set rFind = Me.Range(CurrencyList).Find(What:=Target.Value, LookAt:=xlWhole)
        If Not rFind Is Nothing Then
            For Each rStep In Me.Range(Me.Range(ChangeCellStart), Me.Cells(Me.Rows.Count, Me.Range(ChangeCellStart).Column).End(xlUp))
                On Error Resume Next
                If InStr(1, rStep.Value, "!") = 0 Then
                    Me.Range(rStep.Value).NumberFormat = rFind.Offset(0, 1).NumberFormat
                Else
                    sSheet = Trim(Left(rStep.Value, InStr(1, rStep.Value, "!") - 1))
                    sRange = Trim(Right(rStep.Value, Len(rStep.Value) - InStr(1, rStep.Value, "!")))
                    If Left(sSheet, 1) = "'" Then sSheet = Right(sSheet, Len(sSheet) - 1)
                    If Right(sSheet, 1) = "'" Then sSheet = Left(sSheet, Len(sSheet) - 1)
                    ThisWorkbook.Worksheets(sSheet).Range(sRange).NumberFormat = rFind.Offset(0, 1).NumberFormat
                End If
                On Error GoTo 0
            Next rStep
        End If
    End If
End Sub

Open in new window


Code goes in the same place.

HTH

Zack
0

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
Bright01Author Commented:
Zack,

Great job!  It works on the sample I sent you.  However, it doesn't work in my production version.  And here's potentially why (this may be very simple).  On the sample in column E, when I paste or type the location referencing the other sheet, it works fine and looks like this;

Sheet1!C4

When I try to reference the same location in my production workbook, it adds a character and I cannot get it to not add it.

'Customer - Input!C4

It adds the " ' " for some reason.  Any ideas?

B.
0
Zack BarresseCEOCommented:
Yeah, it's a little quirk of Excel. When you are typing in text, you can set a prefix character which is recognized by Excel, the single apostrophe ' . If you enter that as the first character Excel makes the entire cell text, and here's the kicker - it won't show that character. It's a way to enter numbers as text more or less. So you would need to add another apostrophe to the front. So your value in the cell would actually be typed in as...

''Customer - Input!C4

Make sense? It's odd, I know. The code should have adapted for that though. It does seem to work for me. Are you sure that is the exact sheet name? If it still doesn't work, is there any way you can strip out sensitive data and post a sample of your production file?

Zack
0
Bright01Author Commented:
Wow... you know your stuff.

Here's the problem file.
Change-Format-Model.xlsm
0
Bright01Author Commented:
See above.
0
Zack BarresseCEOCommented:
I can't view the code in lieu of the password. It seems to work for me. ? If you put your value in like this...

'Customer - Inputs'!C9

...it works. Is it not working for you?

Zack
0
Bright01Author Commented:
Still not working for me.

I took all the password protection off.  You will see it change in "Currency" but not on the first tab.

B.
Change-Format-Model.xlsm
0
Zack BarresseCEOCommented:
The sheet name in your cell is...

Customer-Inputs

... but your actual sheet name is ...

Customer - Inputs

The actual name has spaces around the dash.  The sheet name in the cell must match what your sheet name is.

Zack
0
Bright01Author Commented:
Zack,  OUTSTANDING JOB!  Thank you very much and particularly for your patience!  From your comments, I was able to both learn and get it to work.  You are a very good teacher and you know Excel incredibly well.  Again, a sincere, "thank you" for your help.

B.
0
Zack BarresseCEOCommented:
You're very welcome! Glad I could help. Have a great day!

Zack
0
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 Applications

From novice to tech pro — start learning today.