Changing Currency

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...


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

Select allOpen 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

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.

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

Zack

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.

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

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.

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

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

Select allOpen 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

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.

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

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.

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

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.

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.

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

Wow... you know your stuff.

Here's the problem file.
Change-Format-Model.xlsm

See above.

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

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

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

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.

You're very welcome! Glad I could help. Have a great day!

Zack