Solved

Exception for non Currency values

Posted on 2014-01-27
15
218 Views
Last Modified: 2014-02-01
EE Pros,

I have a Demo WS and Currency WS that Gowflow has helped me develop where when you change Currency, or change the Demo, it changes to the correct currency values.  I have a change that needs to be made.  

I have to tell you how this works first:

You select a "Currency" on the Customer-Input Tab and that automatically changes the currency values and formats throughout the model to reflect the right currency value and format.  On the Demo Tab, you can select 3 different types of "Demos" (Conservative, Standard, and Aggressive) that also change the values and echo the new value and format through the model.

Here's what I need.  In the Demos Tab, the values in the Table (Demo WS; col. E, F and G) may not be currency.  They may be % or simply numbers. For this reason, they don't need to be converted.

In the case that the value that is imposed in "I" is not "currency", it needs to be reflected as that value and format in "J".  In otherwords, in the Demo WS, it should only convert currency values; not other values.

That's it.

Thank you in advance,

B.
Demo-Change.xlsm
0
Comment
Question by:Bright01
  • 8
  • 7
15 Comments
 
LVL 80

Expert Comment

by:byundt
ID: 39814038
I tried to figure out how the values were getting changed in Demos column J when you changed the dropdown selection in Demos cell D3, but it wasn't obvious. I am therefore reluctant to suggest the (probably) minor change required to update only items that are expressed in a foreign currency.

That said, I have handled a similar problem in extremely simple fashion by using Styles and conversion formulas.

To get the numeric value, I used a formula like:
=IF(reference value = "", "", reference value * VLOOKUP(foreign currency, currency table, 2,FALSE))
Reference value would be a value in column I that uses US dollars as the currency.

If a cell with numeric value doesn't need conversion, then the formula was:
=IF(reference value = "", reference value)

I used a custom Style (e.g. ForeignCurrency) for all cells that needed to change formatting because of foreign currency. To update their formatting throughout the workbook, it was a simple matter of changing the number format in the style.

If you don't want to have a gazillion VLOOKUP formulas in your workbook, then you could use VBA code to go through each worksheet looking for cells that use the ForeignCurrency style. Each such cell would be converted to the new value and currency.
0
 

Author Comment

by:Bright01
ID: 39814482
Interesting approach.  

So a two perspectives on this;

1.) I've come a long way with Gowflow's help and code to get this where the VBA code automatically does the currency conversion in the Currency WS and passes the value to the Demo WS.  There is a formula in Demos, Col. I, that determines the value selected (i.e. Conservative, Standard, Aggressive).  There is additional VBA code that is located in Module2, 3rd Sub, that updates the demo fields in J.  If any change made to the "Value Selected" (Col. I), runs through the Currency WS and is passed back to the Demo WS in Col. J, "Converted Value".  Also, if any new currency is selected, it also reflects that change in the model WSs.  What I need is for the VBA code to recognize that if the value in the table (i.e. Demos WS, Col. E, F or G) is not a currency (e.g. USD), then the value placed in J is the same value that is reflected in I (e.g. a %, a number, etc.).  In other words, the value is only currency converted, if the value is a Currency in Col. I.

2.) I think what you are suggesting is to use a formula like VLOOKUP to populate Demos, Col. J and then if the value is a currency (i.e. USD), then it runs through the Currency WS.  What is unclear to me is 1.) how to return the value without overwriting the formula (in Col. J) and then 2.) how to populate the Reference cells which the VBA code already does.

If you could give Option 1 another look over, it may be better to start with the approach already underway, unless you feel strongly that approach 2 has more merit and more simplicity.  Gowflow did a really good job in getting me this far....... I owe him a great deal of respect for this.   I appreciate your help in this modification.

B.
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39816503
I redid the workbook along the lines suggested. In the Demos worksheet, I used an INDEX & MATCH formula for column I, and a Worksheet_Change macro to call a tweaked version of sub UpdateDemos. A revised Worksheet_Change sub watches the Customer-Inputs worksheet for changes to SelectedCurrency, and calls UpdateDemos. This Worksheet_Change sub also updates the number format for cells using the ForeignCurrency style.

You didn't have a named range for cell F2 on the Customer-Inputs worksheet. I called it SelectedCurrency so I could refer to it in the code.

I applied the ForeignCurrency style to all cells that had previously used the UK Pound currency symbol on worksheets Customer-Inputs, Price Quote and Demos.

Besides its original function, the UpdateDemos sub now performs a currency conversion on Demos worksheet column J for all cells that use ForeignCurrency style. The sub also copies over the values in column I, in case those were changed.

To make it easy to apply the ForeignCurrency style to cells, sub ApplyForeignCurrencyStyle toggles the cell style between Normal and ForeignCurrency. I then added an icon for that sub to the QAT above the ribbon so I could select a cell, then click on that icon to apply the ForeignCurrency style (or reverse it back to Normal). The change in number format will apply to every cell in the workbook that uses the ForeignCurrency style.

All code not mentioned above is not required. I didn't delete it, though I did put an x in front of the name of any event subs that I wasn't using.

As a result of these code changes, the logic is easy to follow and the UpdateDemos sub does not need to be called multiple times when the user changes the dropdown between Conservative, Standard and Aggressive.
'Goes in code pane for worksheet Customer-Input
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
If Not Intersect(Target, Range("SelectedCurrency")) Is Nothing Then
    Set cel = shCurrency.Range("Currencies").Find(Range("SelectedCurrency").Value, LookAt:=xlWhole, MatchCase:=False)
    ThisWorkbook.Styles("ForeignCurrency").NumberFormat = cel.Offset(0, 1).NumberFormat
    UpdateDemos
End If
End Sub

Open in new window

'This sub goes in worksheet Demos code pane
Public Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range
Set targ = Range("D3:G98")      'Watch these cells for changes
If Not Intersect(targ, Target) Is Nothing Then UpdateDemos
End Sub

Open in new window

'This code goes in a regular module sheet
Sub UpdateDemos()
Dim WS As Worksheet
Dim dc As Range
Dim cel As Range, rgLookup As Range
Dim Sht As String, Rng As String, sCurrency
Dim dConversion As Double

sCurrency = ThisWorkbook.Names("SelectedCurrency").RefersToRange.Value
Set rgLookup = shCurrency.Range("Currencies").Resize(, 2)
dConversion = Application.VLookup(sCurrency, rgLookup, 2, False)
Set WS = shDemos

With Application
    .EnableEvents = False
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

With WS
    For Each cel In .Range("J8:J91")
        If cel.Offset(0, -1).Value = "" Then
            cel.Value = ""
        ElseIf cel.Style = "ForeignCurrency" Then
            cel.Value = cel.Offset(0, -1).Value / dConversion
        Else
            cel.Value = cel.Offset(0, -1).Value
        End If
    Next
    
    Set dc = .Range("6:6").Find(.Range("d3"))
    For Each cel In .Range("d8:d91").SpecialCells(xlCellTypeConstants)
        Sht = Mid(cel, 2, InStr(cel, "!") - 2)
        Rng = Right(cel, Len(cel) - InStr(cel, "!"))
        .Cells(cel.Row, "J").Copy
        Sheets(Sht).Range(Rng).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Next cel
End With

With Application
    .EnableEvents = True
    .DisplayAlerts = True
    .CutCopyMode = False
End With

End Sub

Sub ApplyForeignCurrencyStyle()
'Toggles between Normal and ForeignCurrency styles
Selection.Cells.Style = IIf(ActiveCell.Style = "ForeignCurrency", "Normal", "ForeignCurrency")
End Sub

Open in new window

Demo-ChangeQ28349632.xlsm
0
 

Author Comment

by:Bright01
ID: 39819180
Byundt,

Thank you for the help.  I need to make sure, as I integrate this into the production WB, that it will integrate well.  And I may have to author a different Question as I begin to scale it.  However, I wanted to let you know a couple of potential "gotchas" that I have encountered in my last attempt to scale/integrate this code.
_______________________________________________________________________

I redid the workbook along the lines suggested. In the Demos worksheet, I used an INDEX & MATCH formula for column I, and a Worksheet_Change macro to call a tweaked version of sub UpdateDemos. A revised Worksheet_Change sub watches the Customer-Inputs worksheet for changes to SelectedCurrency, and calls UpdateDemos. This Worksheet_Change sub also updates the number format for cells using the ForeignCurrency style.

Will a "Worksheet_Change sub  (Public or Private) interfere with other changes that happen in the same WS?  There are additional macros in each WS and in the Modules that could potentially interfere with a change outside the specified cell.   Is there a way to make it a change to a specific cell (may have already done this).?  If so, can you note the cell location and I'll change it if I need to?


You didn't have a named range for cell F2 on the Customer-Inputs worksheet. I called it SelectedCurrency so I could refer to it in the code.

Great!

I applied the ForeignCurrency style to all cells that had previously used the UK Pound currency symbol on worksheets Customer-Inputs, Price Quote and Demos.

Keep in mind that the Reference Cells (Col. D) in the Demos WS, directs where the changed value goes.  As I scale this, I will have about 150 Cell References for different WSs.

Besides its original function, the UpdateDemos sub now performs a currency conversion on Demos worksheet column J for all cells that use ForeignCurrency style. The sub also copies over the values in column I, in case those were changed.

To make it easy to apply the ForeignCurrency style to cells, sub ApplyForeignCurrencyStyle toggles the cell style between Normal and ForeignCurrency. I then added an icon for that sub to the QAT above the ribbon so I could select a cell, then click on that icon to apply the ForeignCurrency style (or reverse it back to Normal). The change in number format will apply to every cell in the workbook that uses the ForeignCurrency style.

All code not mentioned above is not required. I didn't delete it, though I did put an x in front of the name of any event subs that I wasn't using.

As a result of these code changes, the logic is easy to follow and the UpdateDemos sub does not need to be called multiple times when the user changes the dropdown between Conservative, Standard and Aggressive
0
 
LVL 80

Expert Comment

by:byundt
ID: 39819468
It makes me feel uneasy as a developer when one sub triggers others indirectly in a cascade of event subs. Code that is built using that approach will be difficult to debug or modify by anyone except the original author--and even then it will be tough.

The better way to approach it is to turn events off while you explicitly call all the required subs. By making the program logic obvious, somebody new can readily see which pieces might need to be changed.

If you look at the two Worksheet_Change event subs I posted, you will see that the first one responds only to changes in named range SelectedCurrency. It then updates the number format for all cells using the ForeignCurrency style, and calls the UpdateDemos sub. The second Worksheet_Change sub watches Demos worksheet cells D3:G98, and calls UpdateDemos if anything changed in that range.

If your other subs change the values in SelectedCurrency or Demos cells D3:G98, then you get back to the cascade of event subs I was initially complaining about. I didn't examine your other subs to see whether this is the case. But it won't take long to search your code for problems or to patch it up if you don't catch issues until debugging phase.

I appreciate that you had a short list of cells being mapped in column D of Demos worksheet. Sub UpdateDemos handles any mapping relationships specified in D8:D91, and is called by the Worksheet_Change sub if you make any changes to those cells during design phase scale-out. If you need to handle 150 mapping relationships, expand the ranges in both the Worksheet_Change sub and UpdateDemos.
0
 

Author Comment

by:Bright01
ID: 39820473
Great comments!  One other question.  As I take the WSs over to my production WB, should I copy/move them over or cut and paste them along with all the coding?  What's the best approach given I have range names, code and WS implications?

Thank you very much for the help and advice.  

B.
0
 

Author Comment

by:Bright01
ID: 39820483
One error I'm recognizing.  The Formats are not updating when I add another row of data and fire off either/both a change in Demos and a Change in Customer-Inputs "Currency".

B.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 80

Expert Comment

by:byundt
ID: 39821044
I'm not reproducing that problem on my sample workbook.

Could you post step by step instructions on how to reproduce the error in either the workbook I posted, or a new one that you post?
0
 

Author Closing Comment

by:Bright01
ID: 39822921
Outstanding work!  Thank you very much!

I had to copy the formatting down as I scaled it and once I did, worked like a charm.

Can you comment on the question above on bringing the code over?  

Thank you again,  B.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39822991
As an alternative, consider making the workbook with the code the destination of the copying rather than the source. Doing so will preserve the named ranges and code.

Select all worksheets in your production workbook that aren't in the one with the code. Rightclick the sheet tab and use the Copy or Move menu item to move them into your other workbook. By selecting them all at once, any formulas between the worksheets will point to their new home.

For worksheets that are in both your production workbook and the one with the code, select the data, copy it and paste in the destination workbook.

Save the resulting file with a new name so you can go back if necessary.

You may need to use the Data...Edit links menu item to point any links back to the original workbook to the copy you just saved. After doing so, there should be no links listed in the Data...Edit links menu item.

I also suggest that you move all subs that weren't in my post into a separate module. You should leave event subs where you found them--I changed their names so they wouldn't run automatically. But any non-event subs that are in a worksheet code pane, plus any subs in a regular module sheet should be moved into a new regular module sheet. The only subs that should end up in Module1 would be UpdateDemos and ApplyForeignCurrencyStyle.

If you find yourself needing some of the subs I wasn't using, you can always copy them over into Module1. Ultimately, you will be able to use the File...Remove module menu item to get rid of the subs that aren't being used.

As you scale-out the mapping relationships, don't forget to apply the ForeignCurrency style to the cells in Demos worksheet column J as well as the cells that were mapped in relationship to them.

Save the workbook again, and then test. You may need to change some of the addresses in the code as you scale things out.
0
 

Author Comment

by:Bright01
ID: 39823483
Byundt,

I've been working on the integration for several hours.  I could not move my production WB into the WB you sent me because it's too large/complicated; so I have attempted to move the code/modules into my production WB.  I've almost got it but ran across one problem.

When I select the Currency on the Customer-Input Tab, I get an error:

"Unable to set number format to property of the style class."

I've traced this to the following code you sent me that's in the Customer-Input WS:

'Goes in code pane for worksheet Customer-Input
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
If Not Intersect(Target, Range("SelectedCurrency")) Is Nothing Then
    Set cel = shCurrency.Range("Currencies").Find(Range("SelectedCurrency").Value, LookAt:=xlWhole, MatchCase:=False)
    ThisWorkbook.Styles("ForeignCurrency").NumberFormat = cel.Offset(0, 1).NumberFormat
    UpdateDemos
End If
End Sub

With the line item:  ThisWorkbook.Styles("Foreign................   HIGHLIGHTED.

I do have code in the ThisWorkbook in my production WB where there is none in the work you sent me back....could this be the problem?

Any assistance would be appreciated.

Thank you,

B.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39823854
1.  Is there a ForeignCurrency style in the production workbook? There must be--create it if you have to.
2.  Does cel have a value when the run-time error occurs?

Regarding code in ThisWorkbook code pane, I can't comment until I see what it is.

If you still aren't able to fix the error, could you either post the file or email it to me? My email address is in my member profile. Don't post the file if it contains business sensitive information, however.
0
 

Author Comment

by:Bright01
ID: 39824067
1.) How do I create a ForeignCurrency style?  There must be one in the sheet you sent me...where would I find it?

2.) Runtime Error 1004.

b.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39824123
You most likely have a ForeignCurrency style if you moved a sheet containing it over into the target workbook. To verify, on the Home menu, choose the Cell Styles dropdown. You should see ForeignCurrency listed.

Your problem is definitely caused by cel not pointing to a cell. Most likely, it is because there is no worksheet with codename shCurrency. Or because named ranges SelectedCurrency and Currencies have a #Ref! error.

I renamed the code names for the worksheets using the Properties pane in the VBA Editor. Look in the (Name) field at the top of the properties list. Change the code names to: shCurrency, shDemos, shCustomerInputs and shPriceQuote. Right now, they are likely Sheet1, Sheet2, Sheet3, etc. The user can't see or change the code name--you have to do it in the VBA Editor. For this reason, using code names is a more robust way of building code.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39826675
Bright01 emailed me the workbook being developed. Upon investigation, I found that a number of worksheets were protected. If those worksheets used the ForeignCurrency style, then it caused a run-time error on the statement trying to change the number format of the ForeignCurrency style.

The cure was to protect each of the worksheets with UserInterfaceOnly property set to True. This allows macros to make changes to protected worksheets, but not users. I set this property when the workbook opened (using a Workbook_Open sub), and also when protection was reapplied by one of Bright01's other macros.

After making the UserInterfaceOnly fix, the user could change the currency and workbook values and formatting would update as expected.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now