RE: Automatically Update Currency

[John Bundy]

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

[Peterwnixon]

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

[Chip Pearson]

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" <peter...@hotmail.com.(donotspam)> wrote in message
news:58B0B79C-2932-45D4...@microsoft.com...

[Peterwnixon]

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

[Peterwnixon]

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

[Susan]

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

[John Bundy]

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.

[Peterwnixon]

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!

[Susan]

:) 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]

peter -
did you try this? or have i completely overwhelmed you?
:)
susan