VBA Code to Change Currency Format

[Jim]

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

[Bob Phillips]

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" <J...@discussions.microsoft.com> wrote in message
news:330099BB-1EEE-4EBD...@microsoft.com...

[Atishoo]

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]

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.

[john]

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]

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]

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 (that’s 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]

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