Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Change Currency Format
I'm building a report book template that I want to use for multiple
currencies (only one per book). The first sheet would have a currency selection box that then format all the relevent cells in the other sheets to the selected currency format. I know how to do it the long way, but was hoping someone could suggest an easy quick solution. Thanks in advance. JIM |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Change Currency Format
Via VBA
Select Case Case "$": Range("A1:A100").NumberFormat = "$#,##0.00;($#,##0.00)" Case "£": Range("A1:A100").NumberFormat = "£#,##0.00;(£#,##0.00)" 'etc End Select -- __________________________________ HTH Bob "Jim" wrote in message ... I'm building a report book template that I want to use for multiple currencies (only one per book). The first sheet would have a currency selection box that then format all the relevent cells in the other sheets to the selected currency format. I know how to do it the long way, but was hoping someone could suggest an easy quick solution. Thanks in advance. JIM |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Change Currency Format
Hm
Im thinking that you create a worksheet to function in one standard curreny (say US Dollar) and then on your other sheets each cell that contains a currency value should be a multiplication of the value in us dollars by the exchange rate set from your opening page. "Jim" wrote: I'm building a report book template that I want to use for multiple currencies (only one per book). The first sheet would have a currency selection box that then format all the relevent cells in the other sheets to the selected currency format. I know how to do it the long way, but was hoping someone could suggest an easy quick solution. Thanks in advance. JIM |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Change Currency Format
Perhaps I wasn't clear, I only want the formating to change, not any of the
values. If I prepare a contract in £, I want all currency cells (hundreds over many sheets) to use £ in the currency format. If the contract is in $ or GEL, I want all the currency cell formats to change to the selected currency. "Atishoo" wrote: Hm Im thinking that you create a worksheet to function in one standard curreny (say US Dollar) and then on your other sheets each cell that contains a currency value should be a multiplication of the value in us dollars by the exchange rate set from your opening page. "Jim" wrote: I'm building a report book template that I want to use for multiple currencies (only one per book). The first sheet would have a currency selection box that then format all the relevent cells in the other sheets to the selected currency format. I know how to do it the long way, but was hoping someone could suggest an easy quick solution. Thanks in advance. JIM |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Change Currency Format
Bob Phillips gave you part of the answer. If you want to cycle through all
your worksheets just enclose his suggestion in a For Loop. Something like follwing should work but you will need to expand the Case test for each currency you want to format For Each sh In ActiveWorkbook.Sheets Select Case Me.ComboBox1.Text '<< assume you have currency symbols in a combobox Case "$" sh.Range("A1:A100").NumberFormat = _ "[$$-409]#,##0.00_ ;[Red]-[$$-409]#,##0.00 " Case "£" sh.Range("A1:A100").NumberFormat = _ "£#,##0.00;(£#,##0.00)" End Select Next sh -- jb "Jim" wrote: Perhaps I wasn't clear, I only want the formating to change, not any of the values. If I prepare a contract in £, I want all currency cells (hundreds over many sheets) to use £ in the currency format. If the contract is in $ or GEL, I want all the currency cell formats to change to the selected currency. "Atishoo" wrote: Hm Im thinking that you create a worksheet to function in one standard curreny (say US Dollar) and then on your other sheets each cell that contains a currency value should be a multiplication of the value in us dollars by the exchange rate set from your opening page. "Jim" wrote: I'm building a report book template that I want to use for multiple currencies (only one per book). The first sheet would have a currency selection box that then format all the relevent cells in the other sheets to the selected currency format. I know how to do it the long way, but was hoping someone could suggest an easy quick solution. Thanks in advance. JIM |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Change Currency Format
Thanks all for you time and answers.
I understand a bit of this, I just want to review. The code checks the value of Case and formats accordingly, I get that. I can add a couple more formats for different currencies. Is there a number limit of different case's? I have figured out how to install a ComboBox, and have named it 'Case', so I'm guessing whatever value is in this box will be assinged to Case which then adjusts the formating accordingly. However I cannot seem to figure out how to populate the ComboBox with different currency choices. Suggestions please...... "john" wrote: Bob Phillips gave you part of the answer. If you want to cycle through all your worksheets just enclose his suggestion in a For Loop. Something like follwing should work but you will need to expand the Case test for each currency you want to format For Each sh In ActiveWorkbook.Sheets Select Case Me.ComboBox1.Text '<< assume you have currency symbols in a combobox Case "$" sh.Range("A1:A100").NumberFormat = _ "[$$-409]#,##0.00_ ;[Red]-[$$-409]#,##0.00 " Case "£" sh.Range("A1:A100").NumberFormat = _ "£#,##0.00;(£#,##0.00)" End Select Next sh -- jb "Jim" wrote: Perhaps I wasn't clear, I only want the formating to change, not any of the values. If I prepare a contract in £, I want all currency cells (hundreds over many sheets) to use £ in the currency format. If the contract is in $ or GEL, I want all the currency cell formats to change to the selected currency. "Atishoo" wrote: Hm Im thinking that you create a worksheet to function in one standard curreny (say US Dollar) and then on your other sheets each cell that contains a currency value should be a multiplication of the value in us dollars by the exchange rate set from your opening page. "Jim" wrote: I'm building a report book template that I want to use for multiple currencies (only one per book). The first sheet would have a currency selection box that then format all the relevent cells in the other sheets to the selected currency format. I know how to do it the long way, but was hoping someone could suggest an easy quick solution. Thanks in advance. JIM |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Change Currency Format
Add a Combobox on your worksheet from the CONTROLS Toolbox €“ ( VIEW
TOOLBARS CONTROL TOOLBOX ) Right Click the name tab & select VIEW CODE from the menu. Paste all code below to sheet code page Private Sub ComboBox1_Change() For Each sh In ActiveWorkbook.Sheets Select Case Me.ComboBox1.Text 'add as many Case tests as required Case "$" sh.Range("A1:A100").NumberFormat = _ "[$$-409]#,##0.00_ ;[Red]-[$$-409]#,##0.00 " Case "£" sh.Range("A1:A100").NumberFormat = _ "£#,##0.00;(£#,##0.00)" End Select Next sh End Sub Private Sub Worksheet_Activate() With Me.ComboBox1 .Clear 'add as many .AddItem (including the period or dot) as needed 'followed by "symbol required" .AddItem "£" .AddItem "$" .ListIndex = 0 End With End Sub Go back to worksheet & turn design mode off (thats the pencil, ruler & protractor symbol on toolbar) If you select another sheet then come back to sheet with combobox it should populate with your symbols. Selecting Different symbol should format all sheets in workbook within your defined range. As far as I am aware €“ there is no limit in the number of Case tests you can do but if I am wrong, I am sure someone will point this out! Hope helpful -- jb "Jim" wrote: Thanks all for you time and answers. I understand a bit of this, I just want to review. The code checks the value of Case and formats accordingly, I get that. I can add a couple more formats for different currencies. Is there a number limit of different case's? I have figured out how to install a ComboBox, and have named it 'Case', so I'm guessing whatever value is in this box will be assinged to Case which then adjusts the formating accordingly. However I cannot seem to figure out how to populate the ComboBox with different currency choices. Suggestions please...... "john" wrote: Bob Phillips gave you part of the answer. If you want to cycle through all your worksheets just enclose his suggestion in a For Loop. Something like follwing should work but you will need to expand the Case test for each currency you want to format For Each sh In ActiveWorkbook.Sheets Select Case Me.ComboBox1.Text '<< assume you have currency symbols in a combobox Case "$" sh.Range("A1:A100").NumberFormat = _ "[$$-409]#,##0.00_ ;[Red]-[$$-409]#,##0.00 " Case "£" sh.Range("A1:A100").NumberFormat = _ "£#,##0.00;(£#,##0.00)" End Select Next sh -- jb "Jim" wrote: Perhaps I wasn't clear, I only want the formating to change, not any of the values. If I prepare a contract in £, I want all currency cells (hundreds over many sheets) to use £ in the currency format. If the contract is in $ or GEL, I want all the currency cell formats to change to the selected currency. "Atishoo" wrote: Hm Im thinking that you create a worksheet to function in one standard curreny (say US Dollar) and then on your other sheets each cell that contains a currency value should be a multiplication of the value in us dollars by the exchange rate set from your opening page. "Jim" wrote: I'm building a report book template that I want to use for multiple currencies (only one per book). The first sheet would have a currency selection box that then format all the relevent cells in the other sheets to the selected currency format. I know how to do it the long way, but was hoping someone could suggest an easy quick solution. Thanks in advance. JIM |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Change Currency Format
John,
Your code worked great, except for one part I'm struggling with. When I select the currency on sheet one, it changes all the formatting on all the sheets correctly. However when I return to the sheet that has the combo box, it automatically resets to "£" which is I'm guessing from the ListIndex = 0 code. I can eliminate this problem by deleting this line of code, however the combo box shows a blank, rather than the selected currency. I can live with this, but I would prefer it show the selected currency. Suggestions? Here is my code below: Private Sub ComboBox1_Change() For Each sh In ActiveWorkbook.Sheets Select Case Me.ComboBox1.Text 'add as many Case tests as required Case "$" sh.Range("A1:A100").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("F2:F3").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("E7").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." Case "£" sh.Range("A1:A100").NumberFormat = _ "£ #,##0.00;[Red]-(£ #,##0.00)" Case "‚¬" sh.Range("A1:A100").NumberFormat = _ "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" Case "GEL" sh.Range("A1:A100").NumberFormat = _ "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" End Select Next sh End Sub Private Sub Worksheet_Activate() With Me.ComboBox1 .Clear 'add as many .AddItem (including the period or dot) as needed 'followed by "symbol required" .AddItem "£" .AddItem "$" .AddItem "GEL" .AddItem "‚¬" .ListIndex = 0 End With End Sub "john" wrote: Add a Combobox on your worksheet from the CONTROLS Toolbox €“ ( VIEW TOOLBARS CONTROL TOOLBOX ) Right Click the name tab & select VIEW CODE from the menu. Paste all code below to sheet code page Private Sub ComboBox1_Change() For Each sh In ActiveWorkbook.Sheets Select Case Me.ComboBox1.Text 'add as many Case tests as required Case "$" sh.Range("A1:A100").NumberFormat = _ "[$$-409]#,##0.00_ ;[Red]-[$$-409]#,##0.00 " Case "£" sh.Range("A1:A100").NumberFormat = _ "£#,##0.00;(£#,##0.00)" End Select Next sh End Sub Private Sub Worksheet_Activate() With Me.ComboBox1 .Clear 'add as many .AddItem (including the period or dot) as needed 'followed by "symbol required" .AddItem "£" .AddItem "$" .ListIndex = 0 End With End Sub Go back to worksheet & turn design mode off (thats the pencil, ruler & protractor symbol on toolbar) If you select another sheet then come back to sheet with combobox it should populate with your symbols. Selecting Different symbol should format all sheets in workbook within your defined range. As far as I am aware €“ there is no limit in the number of Case tests you can do but if I am wrong, I am sure someone will point this out! Hope helpful -- jb "Jim" wrote: Thanks all for you time and answers. I understand a bit of this, I just want to review. The code checks the value of Case and formats accordingly, I get that. I can add a couple more formats for different currencies. Is there a number limit of different case's? I have figured out how to install a ComboBox, and have named it 'Case', so I'm guessing whatever value is in this box will be assinged to Case which then adjusts the formating accordingly. However I cannot seem to figure out how to populate the ComboBox with different currency choices. Suggestions please...... "john" wrote: Bob Phillips gave you part of the answer. If you want to cycle through all your worksheets just enclose his suggestion in a For Loop. Something like follwing should work but you will need to expand the Case test for each currency you want to format For Each sh In ActiveWorkbook.Sheets Select Case Me.ComboBox1.Text '<< assume you have currency symbols in a combobox Case "$" sh.Range("A1:A100").NumberFormat = _ "[$$-409]#,##0.00_ ;[Red]-[$$-409]#,##0.00 " Case "£" sh.Range("A1:A100").NumberFormat = _ "£#,##0.00;(£#,##0.00)" End Select Next sh -- jb "Jim" wrote: Perhaps I wasn't clear, I only want the formating to change, not any of the values. If I prepare a contract in £, I want all currency cells (hundreds over many sheets) to use £ in the currency format. If the contract is in $ or GEL, I want all the currency cell formats to change to the selected currency. "Atishoo" wrote: Hm Im thinking that you create a worksheet to function in one standard curreny (say US Dollar) and then on your other sheets each cell that contains a currency value should be a multiplication of the value in us dollars by the exchange rate set from your opening page. "Jim" wrote: I'm building a report book template that I want to use for multiple currencies (only one per book). The first sheet would have a currency selection box that then format all the relevent cells in the other sheets to the selected currency format. I know how to do it the long way, but was hoping someone could suggest an easy quick solution. Thanks in advance. JIM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to add new currency Symbol in Format/Cell/Currency | Excel Discussion (Misc queries) | |||
how to change currency format into sentence in excel? | Excel Discussion (Misc queries) | |||
Can I change currency format using a switch/formula | Excel Discussion (Misc queries) | |||
Change General Format to Currency Format | Excel Worksheet Functions | |||
why does currency format change to number format? | Excel Discussion (Misc queries) |