Exception for non Currency values
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.
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.
ASKER
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.
Thank you very much for the help and advice.
B.
ASKER
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.
B.
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?
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?
ASKER
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.
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.
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.
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.
ASKER
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"))
Set cel = shCurrency.Range("Currenci
ThisWorkbook.Styles("Forei
UpdateDemos
End If
End Sub
With the line item: ThisWorkbook.Styles("Forei
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.
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"))
Set cel = shCurrency.Range("Currenci
ThisWorkbook.Styles("Forei
UpdateDemos
End If
End Sub
With the line item: ThisWorkbook.Styles("Forei
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.
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.
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.
ASKER
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.
2.) Runtime Error 1004.
b.
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.
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.
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.
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.
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.