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 said:
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 said:
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
:
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.
:
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.
:
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
