Currency Conversion

[DanHegarty]

Hi all,

I have a budget spreadsheet which is currently expressed in euros, i would like a pair of option buttons which would allow me to toggle all the figures between euros and dollars.

Anybody have any ideas?

Thanks

[WinteE]

Hi Dan,

I've made you a sample, hope you're familiar with VBA. If not then feel free to ask for help/explanation.

Cheers,
Erik

[Peter1999]

You could use something like this. Use a validation list (this macro is for USD, GBP, EUR), to be selected in E2, and the number you wish to convert in F3. Hope this is what you need.

Peter1999

Please Login or Register  to view this content.

[DanHegarty]

Hi Guys

Thanks for your help but i'm afraid i'm still struggling.

WintE, when i open your example, the buttons are not functional. I created some new buttons and assigned the appropriate macros to them. Unfortunately when i click on them nothing happens.

Peter, I pasted your code in and then assigned it to a button, but again nothing happened when i pressed the button.

Neither showed any debug faults but nothing happened!

Please let me know if I'm doing something stupid.

Thanks

D

[Peter1999]

Sorry, I read back and understood you were trying to do something else than I proposed. My code will just change the format. Maybe you could combine the two macros....

I tested Eriks' code and it works fine with me. Could you specify what problem shows up?

Peter1999

[WinteE]

Hi Dan, Peter,

The macro security is probably "High". You can change this via 'Tools', 'Macro', 'Security' and select 'Medium'.

Erik

[DanHegarty]

Hi guys,

That works brilliantly now. I was using excel on moc osx and it wasn't working but having tried it on a pc it is fine.

One more problem however, the budget that I am working with is fairly large (roughly 1500 cells). When I use the conversion it locks the computer up for 3 or 4 minutes. This seems crazy given the fact that computers can perform millions of caluclations per second. I can visually see each number change as the macro works along the rows!

One issue may be that there are many blank cells involved, but I want the buttons to cover the whole range in case figures are entered there at a later date.

Really can't say thank you enough.

D

[WinteE]

Hi Dan,

The code will calculate every cell with numeric value in the range, it does not when the cell is empty, contains a string value or has a formula in it.
This makes it possible to run the code on one large range instead of splitting it up in various ranges, in order to avoid errors.

Erik

[DanHegarty]

Have you any idea why it would be taking so long to calculate?

[WinteE]

How many rows and columns are there in the range ?

[DanHegarty]

Around 130 rows x 12 columns, broken up slightly by titles.

[Peter1999]

Try adding this at the start of your macro:

Please Login or Register  to view this content.

And add this at the bottom of the code:

Please Login or Register  to view this content.

Will not win you a lot, but anything helps, right?

Peter

[Seattle_Mike]

Hi Dan,

The code will calculate every cell with numeric value in the range, it does not when the cell is empty, contains a string value or has a formula in it.
This makes it possible to run the code on one large range instead of splitting it up in various ranges, in order to avoid errors.

Erik

Erik, I tried your code in a spreadsheet tool I created. I'm trying to convert values in cells showing U.S. $ values into Euros. The macro fails and when I debug the following line is where it's failing
For Each c In Range("RangeToConvert")

I used your code and only modified the location of the B1 and C3 cells. Some of the cells I want to change do have formulas, so maybe that's the issue.
Thanks...Mike

Any idea of what could be wrong?