Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Update Currency
Peter,
You can change the currency symbol from the Format menu, Cells, then the Number tab of that dialog. Choose "Currency" in the "Category" list, and choose your desired symbol in the "Symbol" drop down. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Peterwnixon" .(donotspam) wrote in message ... Hi John, Thanks for looking at this for me. I am just trying to change the display symbol automatically throughout the workbook i.e. "$10,000" to "£10,000" - so no changes in the actual currency value amount. -- Kind regards, Peter Nixon "John Bundy" wrote: Is this performing a calculation based upon exchange rates or are you converting say Euros to dollars? -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Peterwnixon" wrote: Does anybody know of a why that I can get currency to automatically update throughout a work book. Ideally i would like to have the user select a currency from a drop down list - and then this would then automatically update all currency throughout the workbook. Please help I would greatly appreciate it. -- Kind regards, Peter Nixon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Update Currency
peter -
don't know if you're still monitoring this thread...... what about having option buttons on the spreadsheet (that don't print), showing the available currency symbols? when the user clicks the option button they want, the macro would automatically remove whatever symbol/formatting which was there previously, and reformat it in the new symbol. just a thought............ or a floating modeless userform that would do the same thing....... or one non-printing button that calls up a userform to do that. susan Peterwnixon (donotspam) wrote: Thank you for the response - however, I did know how to change it. But is there a way to pre-set up selected cells e.g. a budget colum on seperate worksheets throughout a workbook - that would automatically change to the desired currency. I.e. by setting up a drop down list I can populate the entire workbook in one click with the correct currency symbol, rather than individually changing the currency symbol from the Format menu, Cells, then the number tab of that dialog. Choose "Currency" in the "Category" list, and choose your desired symbol in the "Symbol" drop down. Very appreciate your help with this. -- Kind regards, Peter Nixon "Peterwnixon" wrote: Dear Chip, Thank you for the response - however, I did know how to change it. But is there a way to pre-set up selected cells e.g. a budget colum on seperate worksheets throughout a workbook - that would automatically change to the desired currency. I.e. by setting up a drop down list I can populate the entire workbook in one click with the correct currency symbol, rather than individually changing the currency symbol from the Format menu, Cells, then the number tab of that dialog. Choose "Currency" in the "Category" list, and choose your desired symbol in the "Symbol" drop down. Very appreciate your help with this. -- Kind regards, Peter Nixon "Chip Pearson" wrote: Peter, You can change the currency symbol from the Format menu, Cells, then the Number tab of that dialog. Choose "Currency" in the "Category" list, and choose your desired symbol in the "Symbol" drop down. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Peterwnixon" .(donotspam) wrote in message ... Hi John, Thanks for looking at this for me. I am just trying to change the display symbol automatically throughout the workbook i.e. "$10,000" to "£10,000" - so no changes in the actual currency value amount. -- Kind regards, Peter Nixon "John Bundy" wrote: Is this performing a calculation based upon exchange rates or are you converting say Euros to dollars? -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Peterwnixon" wrote: Does anybody know of a why that I can get currency to automatically update throughout a work book. Ideally i would like to have the user select a currency from a drop down list - and then this would then automatically update all currency throughout the workbook. Please help I would greatly appreciate it. -- Kind regards, Peter Nixon |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Update Currency
:) sorry for all the technical terms.........
i could do it for you, but i'm not good @ explaining things........ but i can try. insert a new worksheet in your workbook & name it "Summary" (without quotes). the up in your main toolbar, right click & select "Control Toolbox" toolbar. drag it to one side & have it dock itself out of the way. select "option button" on that toolbar (if you hover over it, it'll tell you the names). the cursor will change to a cross. drag that around in your spreadsheet & make the option button. your formula bar will now show "=EMBED("Forms.OptionButton.1","")". right click on the newly-created option button & check "properties". change the CAPTION (not the name) to USDollars & then click out into the spreadsheet. go back to your original worksheet & name the range that encompasses all the cells in which the symbol is to be changed - i'm going to call it ChangeSymbol. at this time, (in my sample) all the amounts in ChangeSymbol are formatted with the pound symbol. go back to the Summary worksheet. double-click on the option button. the visual basic editor will open up and your cursor will be blinking within these 3 statements: Option Explicit Private Sub OptionButton1_Click() <cursor here End Sub copy & paste these 2 statements into the space where your cursor is blinking: Application.Goto Reference:="ChangeSymbol" Selection.NumberFormat = "[$$-409]#,##0.00" OptionButton1.Value = False so now you have: Option Explicit Private Sub OptionButton1_Click() Application.Goto Reference:="ChangeSymbol" Selection.NumberFormat = "[$$-409]#,##0.00" OptionButton1.Value = False End Sub still with me? VBG up in "file", choose "close & return to microsoft excel". at the top of the control toolbox there is a triangle/ruler/pencil button - it's called "design mode." click this to turn off design mode (it turned on automatically when you selected the option button to draw it. now click your USDollars option button. voila! :) the way i got this: Application.Goto Reference:="ChangeSymbol" Selection.NumberFormat = "[$$-409]#,##0.00" was by formatting that range myself, with the macro recorder turned on, and seeing what it wrote to do it. i don't know if this will actually help you do this, but it's a start. obviously you would want to have an option button for each format you want (pounds, etc.). questions? susan Peterwnixon (donotspam) wrote: Thank you for all the constructive replies. Unforunately - my grasp of excel, though burgeoning is not complete. Would it be possible to get an answer in laymans terms - of whether by doing one action on an input sheet - you could change the currency format through out all the different pages in the worksheet. I am tempted to do a work around where there is a ="£"&"text" on each sheet. Where the "£" is driven off a cell in the input sheet. However I was looking for a better solution - that could also be used in the number cells i.e. = £20,000. Thank you for all the great input though, and if I knew what a "floating modeless userform " or "option buttons on the spreadsheet (that don't print)," I may have my answer. A few more years practice and I will know though - so thank you once agian for the input - an dI hope you can help! Kind regards, Peter Nixon "John Bundy" wrote: Will a control H find and replace work to change the symbol? if it does you can do it through code after choosing from the dropdown. -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Susan" wrote: peter - don't know if you're still monitoring this thread...... what about having option buttons on the spreadsheet (that don't print), showing the available currency symbols? when the user clicks the option button they want, the macro would automatically remove whatever symbol/formatting which was there previously, and reformat it in the new symbol. just a thought............ or a floating modeless userform that would do the same thing....... or one non-printing button that calls up a userform to do that. susan Peterwnixon (donotspam) wrote: Thank you for the response - however, I did know how to change it. But is there a way to pre-set up selected cells e.g. a budget colum on seperate worksheets throughout a workbook - that would automatically change to the desired currency. I.e. by setting up a drop down list I can populate the entire workbook in one click with the correct currency symbol, rather than individually changing the currency symbol from the Format menu, Cells, then the number tab of that dialog. Choose "Currency" in the "Category" list, and choose your desired symbol in the "Symbol" drop down. Very appreciate your help with this. -- Kind regards, Peter Nixon "Peterwnixon" wrote: Dear Chip, Thank you for the response - however, I did know how to change it.. But is there a way to pre-set up selected cells e.g. a budget colum on seperate worksheets throughout a workbook - that would automatically change to the desired currency. I.e. by setting up a drop down list I can populate the entire workbook in one click with the correct currency symbol, rather than individually changing the currency symbol from the Format menu, Cells, then the number tab of that dialog. Choose "Currency" in the "Category" list, and choose your desired symbol in the "Symbol" drop down. Very appreciate your help with this. -- Kind regards, Peter Nixon "Chip Pearson" wrote: Peter, You can change the currency symbol from the Format menu, Cells, then the Number tab of that dialog. Choose "Currency" in the "Category" list, and choose your desired symbol in the "Symbol" drop down. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Peterwnixon" .(donotspam) wrote in message ... Hi John, Thanks for looking at this for me. I am just trying to change the display symbol automatically throughout the workbook i.e. "$10,000" to "£10,000" - so no changes in the actual currency value amount. -- Kind regards, Peter Nixon "John Bundy" wrote: Is this performing a calculation based upon exchange rates or are you converting say Euros to dollars? -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Peterwnixon" wrote: Does anybody know of a why that I can get currency to automatically update throughout a work book. Ideally i would like to have the user select a currency from a drop down list - and then this would then automatically update all currency throughout the workbook. Please help I would greatly appreciate it. -- Kind regards, Peter Nixon |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Update Currency
peter -
did you try this? or have i completely overwhelmed you? :) susan Susan wrote: :) sorry for all the technical terms......... i could do it for you, but i'm not good @ explaining things........ but i can try. insert a new worksheet in your workbook & name it "Summary" (without quotes). the up in your main toolbar, right click & select "Control Toolbox" toolbar. drag it to one side & have it dock itself out of the way. select "option button" on that toolbar (if you hover over it, it'll tell you the names). the cursor will change to a cross. drag that around in your spreadsheet & make the option button. your formula bar will now show "=EMBED("Forms.OptionButton.1","")". right click on the newly-created option button & check "properties". change the CAPTION (not the name) to USDollars & then click out into the spreadsheet. go back to your original worksheet & name the range that encompasses all the cells in which the symbol is to be changed - i'm going to call it ChangeSymbol. at this time, (in my sample) all the amounts in ChangeSymbol are formatted with the pound symbol. go back to the Summary worksheet. double-click on the option button. the visual basic editor will open up and your cursor will be blinking within these 3 statements: Option Explicit Private Sub OptionButton1_Click() <cursor here End Sub copy & paste these 2 statements into the space where your cursor is blinking: Application.Goto Reference:="ChangeSymbol" Selection.NumberFormat = "[$$-409]#,##0.00" OptionButton1.Value = False so now you have: Option Explicit Private Sub OptionButton1_Click() Application.Goto Reference:="ChangeSymbol" Selection.NumberFormat = "[$$-409]#,##0.00" OptionButton1.Value = False End Sub still with me? VBG up in "file", choose "close & return to microsoft excel". at the top of the control toolbox there is a triangle/ruler/pencil button - it's called "design mode." click this to turn off design mode (it turned on automatically when you selected the option button to draw it. now click your USDollars option button. voila! :) the way i got this: Application.Goto Reference:="ChangeSymbol" Selection.NumberFormat = "[$$-409]#,##0.00" was by formatting that range myself, with the macro recorder turned on, and seeing what it wrote to do it. i don't know if this will actually help you do this, but it's a start. obviously you would want to have an option button for each format you want (pounds, etc.). questions? susan Peterwnixon (donotspam) wrote: Thank you for all the constructive replies. Unforunately - my grasp of excel, though burgeoning is not complete. Would it be possible to get an answer in laymans terms - of whether by doing one action on an input sheet - you could change the currency format through out all the different pages in the worksheet. I am tempted to do a work around where there is a ="£"&"text" on each sheet. Where the "£" is driven off a cell in the input sheet. However I was looking for a better solution - that could also be used in the number cells i.e. = £20,000. Thank you for all the great input though, and if I knew what a "floating modeless userform " or "option buttons on the spreadsheet (that don't print)," I may have my answer. A few more years practice and I will know though - so thank you once agian for the input - an dI hope you can help! Kind regards, Peter Nixon "John Bundy" wrote: Will a control H find and replace work to change the symbol? if it does you can do it through code after choosing from the dropdown. -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Susan" wrote: peter - don't know if you're still monitoring this thread...... what about having option buttons on the spreadsheet (that don't print), showing the available currency symbols? when the user clicks the option button they want, the macro would automatically remove whatever symbol/formatting which was there previously, and reformat it in the new symbol. just a thought............ or a floating modeless userform that would do the same thing....... or one non-printing button that calls up a userform to do that. susan Peterwnixon (donotspam) wrote: Thank you for the response - however, I did know how to change it.. But is there a way to pre-set up selected cells e.g. a budget colum on seperate worksheets throughout a workbook - that would automatically change to the desired currency. I.e. by setting up a drop down list I can populate the entire workbook in one click with the correct currency symbol, rather than individually changing the currency symbol from the Format menu, Cells, then the number tab of that dialog. Choose "Currency" in the "Category" list, and choose your desired symbol in the "Symbol" drop down. Very appreciate your help with this. -- Kind regards, Peter Nixon "Peterwnixon" wrote: Dear Chip, Thank you for the response - however, I did know how to change it. But is there a way to pre-set up selected cells e.g. a budget colum on seperate worksheets throughout a workbook - that would automatically change to the desired currency. I.e. by setting up a drop down list I can populate the entire workbook in one click with the correct currency symbol, rather than individually changing the currency symbol from the Format menu, Cells, then the number tab of that dialog. Choose "Currency" in the "Category" list, and choose your desired symbol in the "Symbol" drop down. Very appreciate your help with this. -- Kind regards, Peter Nixon "Chip Pearson" wrote: Peter, You can change the currency symbol from the Format menu, Cells, then the Number tab of that dialog. Choose "Currency" in the "Category" list, and choose your desired symbol in the "Symbol" drop down. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Peterwnixon" .(donotspam) wrote in message ... Hi John, Thanks for looking at this for me. I am just trying to change the display symbol automatically throughout the workbook i.e. "$10,000" to "£10,000" - so no changes in the actual currency value amount. -- Kind regards, Peter Nixon "John Bundy" wrote: Is this performing a calculation based upon exchange rates or are you converting say Euros to dollars? -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Peterwnixon" wrote: Does anybody know of a why that I can get currency to automatically update throughout a work book. Ideally i would like to have the user select a currency from a drop down list - and then this would then automatically update all currency throughout the workbook. Please help I would greatly appreciate it. -- Kind regards, Peter Nixon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update one worksheet tab to automatically update all other tabs? | Excel Worksheet Functions | |||
how to update list of Currency Symbol on Excel such Asiapac | Excel Discussion (Misc queries) | |||
Automatic Update of Currency Symbol Throughout Workbook | Excel Discussion (Misc queries) | |||
read number/currency automatically | Excel Discussion (Misc queries) | |||
Macro to update based on format? (Currency) | Excel Programming |